SQL: How to get the enough days of events such that I have at least 50 events
Clash Royale CLAN TAG#URR8PPP
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?
1 Answer
1
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:
date
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
);
@Relman . . . As the answer points out, your query would not work if the
date had a time component. The second version addresses this issue.– Gordon Linoff
Aug 12 at 19:02
date
I worked out the problem with my code: The above algorithm that you confirmed was correct. The issue was that further on my django code was filtering the results further and decreasing the list of returned results.
– akraines
Aug 13 at 11:48
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Thanks for taking the time to respond. As far as I can see, your query is basically the same as mine except you removed the "distinct" keyword, and made the subquery refer to events as e2 instead of as as e. However, I only posted posted the main part of the query that I made django generate for me - ie the query itself "worked" but didn't return the expected result. How do the changes you made solve the issue? BTW, ther is no time component on my dates.
– akraines
Aug 12 at 18:49