How to find out the number of matching values ​​from two different tables?

Question

SQL

Hello) The essence of my task is as follows - there are tables clients and orders, which store information about clients and orders, respectively. Clients store user_id and registration date (registered_datetime), orders also store user_id and order start date (when_start). So, I need to find out the number of user_ids whose registration day and order day are the same (September 2021 only). Tell me how can I do this?

Answer 1

what DBMS!?
Different DBMS have their own methods / functions for working with dates, but as I understand it, you store date + time !?
and the SQL syntax can be special too...
assuming it's MySQL , then use the DATE() function to extract the date.
PS: well, either rewrite it yourself, respectively
, through INNER JOIN

SELECT count(DISTINCT c.user_id) AS first_day_order
  FROM clients c
  INNER JOIN orders o ON c.user_id = o.user_id
  WHERE DATE(c.registered_datetime) = DATE(o.when_start)
    AND DATE(c.registered_datetime) BETWEEN '2021-09-01' AND '2021-09-30'
;

or via EXISTS

SELECT count(c.user_id) AS first_day_order
  FROM clients c
  WHERE DATE(c.registered_datetime) BETWEEN '2021-09-01' AND '2021-09-30'
    AND EXISTS (
          SELECT 1 FROM orders o
            WHERE c.user_id = o.user_id
              AND DATE(c.registered_datetime) = DATE(o.when_start)
        )
;

Answer 2

Learning to use the WHERE EXISTS / NOT EXISTS construct

select count(id) users_with_first_day_order
from users
where exists (
  select 1 from orders where orders.user_id = users.id and orders.order_date = users.registration_date
);

MySQL WHERE EXISTS test