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

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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top