LeetCode 512: Game Play Analysis II [SQL]

[@mickyman753]

select player_id, device_id from Activity where (player_id, event_date) in (select player_id, min(event_date) from Activity group by player_id);

[@rajatchopra1411]

not free to solve :(

[@hsrboys9993]

Great explanation using the window functions

[@ManishAshtikar-jv5tq]

@Freferikmuller Can you solve this in SQL Server?

[@kirthikka952]

select player_id, device_id from Activity group by player_id is this correct?

[@maheshodedra8609]

Hey Fredrik, Thanks for the great video, Can't we use below query ? select player_id,device_id from Activity where (player_id,event_date) in (select player_id,min(event_date) as event_date from Activity group by player_id) Looking forward to your response. Thanks

[@frederikmuller]

Yes, that's a viable solution and probably all that's needed for that question. I got into the habit of using window functions for that question type because it's nice to have a flexible solution for some of these and change up the rank or row number filter. That being said, your solution is similar to the first in this video, it's just using WHERE IN instead of a join which essentially does the same thing in this case.

[@dhartishkhatri6240]

Great video thank you for second solution, it can be used in many different ways.

[@souravmoha2224]

Quick Question Fredrik, How this query run in your system, I believe we cant use other columns apart from aggregate functions in select clause , if we are using group by , we can only select the columns we are grouping by, Let me know if i am making any mistake here select player_id, min(event_dt),device_id from activity group by player_id Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 2:32 Expression not in GROUP BY key 'device_id'

[@frederikmuller]

I’m using MySQL in this video which doesn’t have this restriction. You would have to rearrange the query in another SQL dialect.

[@vickyzhang820]

Why we need to add "lookup" at the end of syntax?

[@frederikmuller]

it's just a name for the table created by the subquery, you can choose any name you want

[@anushreepatil4811]

What's wrong with this query? Select player_id, Device_id from activity group by player_id having min(event_date)

[@frederikmuller]

the having clause

[@watchlistsclips3196]

@@frederikmuller What's the problem if we have having clause

[@robinsharma9807]

Hi Sir, can you please explain why this would be wrong select player_id, device_id,min(event_date) as event_date from activity group by player_id, device_id

[@imdeepu7855]

Editor accepts only the expected output, which are player_id and device_id as end columns of output and group by device_id won't be work here. As per the table data, device_type may change in each player itself. so can't be take device_id on group by field.