SQL Challenges/Game Play Analysis IV

Challenge DetailMediumReviewed 2026-04-20

Game Play Analysis IV

Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.

AggregationDate LogicJoinWindow Functions

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-06Medium

Game Play Analysis IV

Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.

Available Tables:

SQL Editor

What This Challenge Is Testing

This is a retention query in miniature. The same logic sits underneath onboarding dashboards, product stickiness metrics, and lifecycle reporting where you need to anchor on a user’s first activity and then test whether they came back in a specific follow-up window.

Topic Tags

AnalyticsDatesWindow Functions

Schema At A Glance

activity

player_iddevice_idevent_dategames_played

Solution Approaches

Find first login, then join to day-plus-one activity

WITH first_logins AS (
  SELECT
    player_id,
    MIN(event_date) AS first_login_date
  FROM activity
  GROUP BY player_id
)
SELECT
  ROUND(
    COUNT(DISTINCT a2.player_id) * 1.0
    / COUNT(DISTINCT f.player_id),
    2
  ) AS fraction
FROM first_logins f
LEFT JOIN activity a2
  ON a2.player_id = f.player_id
 AND DATE(a2.event_date) = DATE(f.first_login_date, '+1 day')

This is the clearest retention pattern. It anchors every player on their first login date, then checks whether a qualifying return event exists exactly one day later.

Use window functions to mark each player start

WITH tagged_activity AS (
  SELECT
    player_id,
    event_date,
    MIN(event_date) OVER (PARTITION BY player_id) AS first_login_date
  FROM activity
)
SELECT
  ROUND(
    COUNT(DISTINCT CASE
      WHEN DATE(event_date) = DATE(first_login_date, '+1 day') THEN player_id
    END) * 1.0
    / COUNT(DISTINCT player_id),
    2
  ) AS fraction
FROM tagged_activity

The window-function version keeps the logic in one pass over the activity table. It is especially useful when you later want several retention checkpoints like day 1, day 7, and day 30 from the same base relation.

Common Pitfalls

Do not measure return rate against all activity rows. The denominator must be distinct players, anchored on their first login. Another mistake is checking for any later login instead of the next-day login specifically.

Frequently Asked Questions

Is this a retention problem or a join problem?

It is primarily a retention problem. The join or window function is just the mechanism for checking whether a qualifying return event exists relative to each player’s first activity.

Why use DISTINCT in the numerator?

Because a player might have multiple qualifying rows or duplicate events. The metric is about the number of players retained, not the number of qualifying event rows.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

Cohort Analysis with SQL

Learn how to perform cohort analysis using SQL to track user retention, identify trends, and measure product success over time.

Time Series Analysis with SQL: Trends, Growth, and Moving Averages

Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.

Understanding SQL Window Functions: A Visual Guide

Master ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Immediate Food Delivery IIMedium

Measure what share of customers got an immediate delivery on their first-ever order instead of across all orders.

Monthly Transactions IMedium

Roll monthly transaction reporting into one query while splitting total volume from approved volume by country.

Rising TemperatureMedium

Compare each weather row with the previous day and return only the IDs where temperature increased day over day.

SQL Challenges/Game Play Analysis IV

Challenge DetailMediumReviewed 2026-04-20

Game Play Analysis IV

Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.

AggregationDate LogicJoinWindow Functions

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-06Medium

Game Play Analysis IV

Write an SQL query to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.

Available Tables:

SQL Editor

What This Challenge Is Testing

Topic Tags

AnalyticsDatesWindow Functions

Schema At A Glance

activity

player_iddevice_idevent_dategames_played

Solution Approaches

Find first login, then join to day-plus-one activity

WITH first_logins AS (
  SELECT
    player_id,
    MIN(event_date) AS first_login_date
  FROM activity
  GROUP BY player_id
)
SELECT
  ROUND(
    COUNT(DISTINCT a2.player_id) * 1.0
    / COUNT(DISTINCT f.player_id),
    2
  ) AS fraction
FROM first_logins f
LEFT JOIN activity a2
  ON a2.player_id = f.player_id
 AND DATE(a2.event_date) = DATE(f.first_login_date, '+1 day')

This is the clearest retention pattern. It anchors every player on their first login date, then checks whether a qualifying return event exists exactly one day later.

Use window functions to mark each player start

WITH tagged_activity AS (
  SELECT
    player_id,
    event_date,
    MIN(event_date) OVER (PARTITION BY player_id) AS first_login_date
  FROM activity
)
SELECT
  ROUND(
    COUNT(DISTINCT CASE
      WHEN DATE(event_date) = DATE(first_login_date, '+1 day') THEN player_id
    END) * 1.0
    / COUNT(DISTINCT player_id),
    2
  ) AS fraction
FROM tagged_activity

Common Pitfalls

Frequently Asked Questions

Is this a retention problem or a join problem?

It is primarily a retention problem. The join or window function is just the mechanism for checking whether a qualifying return event exists relative to each player’s first activity.

Why use DISTINCT in the numerator?

Because a player might have multiple qualifying rows or duplicate events. The metric is about the number of players retained, not the number of qualifying event rows.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

Cohort Analysis with SQL

Learn how to perform cohort analysis using SQL to track user retention, identify trends, and measure product success over time.

Time Series Analysis with SQL: Trends, Growth, and Moving Averages

Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.

Understanding SQL Window Functions: A Visual Guide

Master ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Immediate Food Delivery IIMedium

Measure what share of customers got an immediate delivery on their first-ever order instead of across all orders.

Monthly Transactions IMedium

Roll monthly transaction reporting into one query while splitting total volume from approved volume by country.

Rising TemperatureMedium

Compare each weather row with the previous day and return only the IDs where temperature increased day over day.