What is the best way to do this query (SQL)

Question

i need to do a query but i can't find a good way to do it efficiently.

Everytime a client listen a song , one line is registered with his key and the track_id , date ....

I want to check for every clients if the track he listened the last month are new (never listened before this month so)

One line look like this:

key | track_id | date asd | 12312 | 12/02/2020 fds | 12323 | 12/05/2020

etc

I think i can do something with window functions but i can't seem to find a good way to do it .

Then i also need to get the top 3 most listened song from this list, which i can just do with a window function i guess .

If someone can help me ? Thank you very much.

I don't know , its very heavy db , nothing i did even get a result . I tried to get track_id where in ( select track_id that are older than a month) But i can't group by it . I also tried to count the amount of listen by track_id for each key before 1 month ago and then check for each track_id , key if the count is > 0 — echo55
– echo55, Commented Apr 9, 2020 at 14:24

Gordon Linoff · Accepted Answer · 2020-04-09 14:20:45Z

Use aggregation and window functions:

select key, count(*) as num_rows, count(*) as num_tracks, sum( case when first_yyyymm = yyyymm then 1 else 0 end) as num_new_tracks, sum( case when first_yyyymm < yyyymm then 1 else 0 end) as num_prev_tracks from (select t.key, track_id, date_trunc('month', date) as yyyymm, min(date_trunc('month', date) ) over (partition by key, track_id) as first_yyyymm from t group by key, track_id, yyyymm ) t where yyyymm >= date_trunc('month', current_date) group by key

Sandro · Accepted Answer · 2020-04-13 22:13:30Z

one piece of advice. if you want help from people, always include the DDL for your sample data set. Its not fair to expect people to do it for you. (In this case I did)

create table sandbox.songs (key varchar, track_id number, dt date); insert into sandbox.songs values ('asd', 12312, '12/02/2020'), ('fds', 12323, '12/05/2020'), ('asd', 11000, '04/05/2020'), ('fds', 92823, '04/07/2020'), ('asd', 11000, '3/05/2020'), ('fds', 12323, '12/05/2020'), ('asd', 92834, '3/05/2020');

QUESTION 1

select key, count(0), sum(case when dt < current_date - 30 then 1 else 0 end) as older_than_30, sum(case when dt >= current_date - 30 then 1 else 0 end) as within_30 from sandbox.songs group by key;

[enter image description here][1]

QUESTION 2

track_id, count(0) from sandbox.songs group by track_id order by count(0) desc limit 3; [enter image description here][2] [1]: https://i.sstatic.net/CeDqw.png [2]: https://i.sstatic.net/sdKV0.png

Collectives™ on Stack Overflow

What is the best way to do this query (SQL)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related