Top Score Per Air Apps User

Task

You are given user and gameplay data from an Air Apps mobile product. Write a PostgreSQL query that returns the user or users with the highest valid score. A score is valid only when the related game session is marked completed = true. Include the user's name, app name, and highest valid score. If multiple users tie for the top score, return all of them.

Schema

Table	Column	Type	Description
users	user_id	INT	Primary key for each user
users	user_name	VARCHAR(100)	User display name
users	app_id	INT	App the user belongs to
air_apps	app_id	INT	Primary key for each Air Apps product
air_apps	app_name	VARCHAR(100)	Air Apps product name
game_scores	score_id	INT	Primary key for each score row
game_scores	user_id	INT	User who earned the score
game_scores	score	INT	Score recorded for the session
game_scores	completed	BOOLEAN	Whether the session was completed
game_scores	played_at	DATE	Date the score was recorded

Sample data

user_id	user_name	app_name	score	completed	played_at
2	Ben Ortiz	Translate Now	980	true	2024-03-02
4	Diego Park	Widgetsmith	980	true	2024-03-04
6	Farah Khan	Translate Now	1200	false	2024-03-05
3	Chloe Kim	Widgetsmith	870	true	2024-03-03

Expected output

user_name	app_name	highest_score
Ben Ortiz	Translate Now	980
Diego Park	Widgetsmith	980

Task

Schema

Table	Column	Type	Description
users	user_id	INT	Primary key for each user
users	user_name	VARCHAR(100)	User display name
users	app_id	INT	App the user belongs to
air_apps	app_id	INT	Primary key for each Air Apps product
air_apps	app_name	VARCHAR(100)	Air Apps product name
game_scores	score_id	INT	Primary key for each score row
game_scores	user_id	INT	User who earned the score
game_scores	score	INT	Score recorded for the session
game_scores	completed	BOOLEAN	Whether the session was completed
game_scores	played_at	DATE	Date the score was recorded

Sample data

user_id	user_name	app_name	score	completed	played_at
2	Ben Ortiz	Translate Now	980	true	2024-03-02
4	Diego Park	Widgetsmith	980	true	2024-03-04
6	Farah Khan	Translate Now	1200	false	2024-03-05
3	Chloe Kim	Widgetsmith	870	true	2024-03-03

Expected output

user_name	app_name	highest_score
Ben Ortiz	Translate Now	980
Diego Park	Widgetsmith	980

Task

Schema

Table	Column	Type	Description
users	user_id	INT	Primary key for each user
users	user_name	VARCHAR(100)	User display name
users	app_id	INT	App the user belongs to
air_apps	app_id	INT	Primary key for each Air Apps product
air_apps	app_name	VARCHAR(100)	Air Apps product name
game_scores	score_id	INT	Primary key for each score row
game_scores	user_id	INT	User who earned the score
game_scores	score	INT	Score recorded for the session
game_scores	completed	BOOLEAN	Whether the session was completed
game_scores	played_at	DATE	Date the score was recorded

Sample data

user_id	user_name	app_name	score	completed	played_at
2	Ben Ortiz	Translate Now	980	true	2024-03-02
4	Diego Park	Widgetsmith	980	true	2024-03-04
6	Farah Khan	Translate Now	1200	false	2024-03-05
3	Chloe Kim	Widgetsmith	870	true	2024-03-03

Expected output

user_name	app_name	highest_score
Ben Ortiz	Translate Now	980
Diego Park	Widgetsmith	980

Task

Schema

Table	Column	Type	Description
users	user_id	INT	Primary key for each user
users	user_name	VARCHAR(100)	User display name
users	app_id	INT	App the user belongs to
air_apps	app_id	INT	Primary key for each Air Apps product
air_apps	app_name	VARCHAR(100)	Air Apps product name
game_scores	score_id	INT	Primary key for each score row
game_scores	user_id	INT	User who earned the score
game_scores	score	INT	Score recorded for the session
game_scores	completed	BOOLEAN	Whether the session was completed
game_scores	played_at	DATE	Date the score was recorded

Sample data

user_id	user_name	app_name	score	completed	played_at
2	Ben Ortiz	Translate Now	980	true	2024-03-02
4	Diego Park	Widgetsmith	980	true	2024-03-04
6	Farah Khan	Translate Now	1200	false	2024-03-05
3	Chloe Kim	Widgetsmith	870	true	2024-03-03

Expected output

user_name	app_name	highest_score
Ben Ortiz	Translate Now	980
Diego Park	Widgetsmith	980

Interview Guides

Task

Schema

Sample data

Expected output

Top Score Per Air Apps User

Task

Schema

Sample data

Expected output

Practice SQL

Top Score Per Air Apps User

Task

Schema

Sample data

Expected output

Top Score Per Air Apps User

Task

Schema

Sample data

Expected output

Practice SQL