Question 21294 in Mysql Question posted in
The official MySQL documentation can be found here.

Time Limit Exceeded on Leetcode 512. Game Play Analysis II – Mysql

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
Tags:

3

Answers


  1. 0

    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 ;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search