Given the following schema:

users

entries

How would I query a list of users who have created n entries every day between two dates. Eg, all users who have entries on 10 days between 1 September and 15 September.

Note, users may have multiple entries on a single day. I am interested on entries on distinct days between two dates.

I am using Postgres but I suppose this is a broader sql question.

CodePudding user response：

select u.id as user_id, max(u.username), count(e.id) as entries_count
from users u
    inner join (
      select max(id) as id, user_id, max(inserted_at) as inserted_at
      from entries
      group by user_id cast(inserted_at as date) 
    ) e on u.id = e.user_id
where e. inserted_at between {start_date} and {end_date}
group by u.id having count(e.id)>X

{start_date} => start of time range

{end_date} => end of time range

X => how manny entries there should be at a minimum

The timerange here is inclusive