SQL LeetCode: 511. Game Play Analysis I

Pallavi Mirajkar Dantkale
2 min readApr 5, 2021

--

511. Game Play Analysis I

SQL Schema

Table: Activity

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| 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 game.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

Write an SQL query that reports the first login date for each player.

The query result format is in the following example:

Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result table:
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+
  1. Using group by

SELECT
player_id, min(event_date) as first_login
FROM
Activity
group by player_id ;

2. Using Windows function

SELECT
player_id,
event_date as first_login
FROM
(

SELECT
player_id,
event_date,
dense_rank() OVER (PARTITION BY player_id order by event_date) as denserank_date
FROM
Activity ) as rank_date
WHERE denserank_date = 1;

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

--

--

Pallavi Mirajkar Dantkale
Pallavi Mirajkar Dantkale

Written by Pallavi Mirajkar Dantkale

QA Engineer / Data Analyst — Highly committed to Quality Assurance and data analysis, advocate for quality and add the right value to the organization.

No responses yet

Write a response