550. Game Play Analysis IV

SQL 50 · Basic Aggregate Functions (day 3). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

MediumSolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One number: fraction of players who logged in the day after their first login.

2. Approach

Find each player’s MIN(event_date), keep logins on that date + 1 (tuple IN with DATE_SUB), divide by total players.Find each player’s MIN(event_date), keep logins on that date + 1 (tuple IN with event_date - 1), divide by total players.

3. The trap

Denominator is distinct players, not rows. ROUND to 2.Denominator is distinct players. COUNT / COUNT is integer division here: cast to numeric.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

SELECT ROUND(
  COUNT(*) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY)) IN (
  SELECT player_id, MIN(event_date)
  FROM Activity
  GROUP BY player_id
);SELECT ROUND(
  COUNT(*)::numeric / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, event_date - 1) IN (
  SELECT player_id, MIN(event_date)
  FROM Activity
  GROUP BY player_id
);

Patterns used

Phrase finder · MySQL vs PostgreSQL