SQL Write a request that returns: Customers – only who have had at least one order every day in the last month [closed]

Dynamics of the number of orders by customers who created at least one order every day.

You have an orders table with columns order_id (order id), created (order creation date, datetime format), user_id (id of the customer who created the order).

Write a request that returns: Customers – only who have had at least one order every day in the last month.

Have you tried this, but don’t know how to set a condition for every day?

Answer

Here is one method:

select user_id
from orders
where created >= date_format(current_date, '%Y-%m-01')
  and created <  date_format(current_date, '%Y-%m-01') + interval 1 month
group by user_id
having count(distinct date(created)) 
    = day(date_format(current_date, '%Y-%m-01') + interval 1 month - interval 1 day)

How this works:

• the where clause filters on the previous month

• then we group by user_id

• finally, the having clause counts how many distinct days appear in each group, and ensures that it is equal to the number of days in the month; to compute the number of days within last month, we use date arithmetics: basically this is equal to the day number of the last day of the month

Disclaimer: I read this as a MySQL question, but now I see that there is no such tag in your question. The logic would be the same in other databases, you would just need to adjust the date arithmetics logic.