CODEWITHSUNDEEP
sqlpostgresql
akraines
akraines

Reputation: 2045

SQL: How to get the enough days of events such that I have at least 50 events

I have a table of events. Each events has a date. My API requires that I return at least X events before/after a certain date, but I must return all the events for each date. If I did this in code I'd do something like group the events by date and sorted ascending/descending then take enough days such that I have at least 50 events.

This is what I have so far:

.... FROM "events" WHERE (date in ( SELECT DISTINCT date FROM events WHERE DATE <= 2018-08-12 ORDER BY date DESC LIMIT 50))

However it only returns a 16 events even though there are many more in the DB.

I think that for this to work I need to do something like getting the count of events per day and then getting enough days such that the sum of events >= X (eg. 50) and then return the actual events. Is that how to do it? And how should such a query be written correctly?

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This code should work:

select e.*
from events e
where date in (select e2.date
               from events e2
               where e2.date <= '2018-08-12'
               order by e2.date desc
               limit 50
              );

The one reason it would not work is if date really had a time component. If that is the case, then casting the value would fix the problem:

select e.*
from events e
where date::date in (select e2.date::date
                     from events e2
                     where e2.date <= '2018-08-12'
                     order by e2.date desc
                     limit 50
                    );

Upvotes: 1

Related Questions