Time Limit Exceeded on Leetcode 512. Game Play Analysis II-CodePudding

Table information:

| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
 -------------- ---------

(player_id, event_date) is the primary key of this table. This table shows the activity of players of some games. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Query requested is:

Write an SQL query to report the device that is first logged in for each player.

My solution :

select a1.player_id, a1.device_id
from Activity a1
where event_date = (
    select min(event_date) from Activity a2
    where a1.player_id = a2.player_id
    group by a2.player_id
)

Sorry i couldn't get the query properly formatted.

The problem & question:

For some reason, it passes the initial tests, but when I submit the solution, I get "Time limit exceeded". Is there a certain logic portion in my query that is inherently bad/inefficient? What could be wrong?

I tried looking it up on the platform but couldn't find any information. Thanks in advance!

EDIT: I added explain because some of you asked. I don't understand the full meaning of it, but maybe it helps.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	a1		ALL					5	100	Using where
2	DEPENDENT SUBQUERY	a2		ALL					5	20	Using where; Using temporary

CodePudding user response：

You should be able to do something like:

SELECT 
    player_id,
    FIRST_VALUE(device_id) OVER ( PARTITION BY player_id ORDER BY event_date ) device_id
FROM Activity

which will hopefully meet your performance requirements.

Letting the engine handle as much as it's optimiser can with the features that it provides, without trying to second-guess it, will typically perform better than the most obvious, heavy-handed solution.

CodePudding user response：

This one can be useful using inner join, Its working for mysql <=8

select a1.player_id, a1.device_id
from Activity a1
inner join (
    select player_id, min(event_date) as event_date
    from Activity
    group by player_id
) as s on s.event_date = a1.event_date and a1.player_id = s.player_id

You can check it from here : https://dbfiddle.uk/ebBce-Fn

CodePudding user response：

The query could be written more efficiently (MySQL 8.0 needed) as follows

select  player_id, 
        device_id
from ( select player_id,
              device_id,
              row_number() over(partition by player_id order by event_date asc ) as rn 
       from Activity 
     ) tbl
where rn=1;

Edit.

Add the following index

ALTER TABLE Activity  ADD INDEX `pl_eve`(`player_id`,`event_date`);

I am more curious what in my solution's logic is not working properly

event_date = expects that the subquery returns only one value which will not happen due to the group by player_id on the subquery considering that more than two distincts player_id exists on table.

The correct query using your logic would be the query on @SelVazi answer. In the subquery you find the minimum date per each player_id , and use both values on the join condition to find the device_id. An index on (event_date,player_id) would speed things up

select a1.player_id, 
       a1.device_id
from Activity a1
inner join ( select player_id, 
                    min(event_date) as event_date
             from Activity
             group by player_id
            ) as s on s.event_date = a1.event_date and a1.player_id = s.player_id ;