Here's another fun query i found to train SQL for product interviews. I am really doubtful about it tho -
given the following table
tab_a: user_id, date (they entered platform)
find the top 100 users with the longest continuous streak of visiting the platform as of yesterday.
I defined "longest continuous streak" as the gap between two timestamps of the same user.
Here's my solution using a self join, anyone has any better idea?
select a.user_id,
age(a.date, b.date) as streak
from tab_a as a
inner join tab_a as b
using(user_id)
and a.date > date 'yesterday' -- PostgreSQL 'yesterday' syntactic sugar
order by streak desc
limit 100;
This would be a mess as a comment. I am afraid you are not ready for an interview if you don't know what lateral is. Anyway, you might not need it and your question is not clear to understand what you are asking. I have 2 queries for you and not sure if you meant one of them:
with visits as
(
select user_id, min(visit) as firstVisit, max(visit) as lastVisit
from tab_a
group by user_id
)
select user_id, age(lastVisit, firstVisit)
from visits;
with visits as (
select user_id, visit,
coalesce(age(visit, lag(visit,1) over (partition by user_id order by visit)), '0'::interval) as VisitAge
from tab_a
)
select user_id, visit, VisitAge
from visits
order by VisitAge desc
limit 5;
I created a DbFiddle Demo for you to test. Hope this helps.