top 100 users online since yesterday postgres

Question

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;

You might use lateral. Your SQL is not understandable and I doubt it would work. — Cetin Basoz, Aug 20 '20 at 10:08
I tried to make the join more explicit. What is lateral? Your comment is not very helpful as is. — Tytire Recubans, Aug 20 '20 at 10:12
Oh you don't know lateral! You can provide some sample data and desired output to start with so we can give better answers. What is a.date in the first place when tab_a doesn't have such a field for example? — Cetin Basoz, Aug 20 '20 at 10:15
No i don't know lateral, but I happy to go and learn about it. The whole point of thought experiment queries is that they are thought experiments asked in interviews without mock data. Point taken and corrected on the date/timestamp col names. — Tytire Recubans, Aug 20 '20 at 10:18

Cetin Basoz · Accepted Answer · 2020-08-20T10:43:13.027

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.

top 100 users online since yesterday postgres

1 Answers1