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);
@rajatchopra14112 жыл бұрын
not free to solve :(
@hsrboys99933 жыл бұрын
Great explanation using the window functions
@ManishAshtikar-jv5tq Жыл бұрын
@Freferikmuller Can you solve this in SQL Server?
@kirthikka9522 жыл бұрын
select player_id, device_id from Activity group by player_id is this correct?
@maheshodedra86093 жыл бұрын
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
@frederikmuller3 жыл бұрын
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.
@dhartishkhatri62403 жыл бұрын
Great video thank you for second solution, it can be used in many different ways.
@souravmoha22242 жыл бұрын
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'
@frederikmuller2 жыл бұрын
I’m using MySQL in this video which doesn’t have this restriction. You would have to rearrange the query in another SQL dialect.
@vickyzhang8203 жыл бұрын
Why we need to add "lookup" at the end of syntax?
@frederikmuller3 жыл бұрын
it's just a name for the table created by the subquery, you can choose any name you want
@anushreepatil48113 жыл бұрын
What's wrong with this query? Select player_id, Device_id from activity group by player_id having min(event_date)
@frederikmuller3 жыл бұрын
the having clause
@watchlistsclips31963 жыл бұрын
@@frederikmuller What's the problem if we have having clause
@robinsharma98073 жыл бұрын
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
@imdeepu78552 жыл бұрын
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.