AlgoMaster Logo

Exercise: Common Table Expressions (CTEs)

Last Updated: May 12, 2026

1 min read

For this exercise, we will work with below data:

SQL

Exercise 1

Easy

Using a single CTE, find users whose total duration_listened exceeds 700 seconds. Show the username and total_listened, ordered by total_listened descending.

SQL

Expected Query

Expected Output

Exercise 2

Easy

Using a single CTE that filters users to the premium tier, count how many streams those users have generated in total. Show one row with the column premium_stream_count.

SQL

Expected Query

Expected Output

Exercise 3

Medium

Using two independent CTEs (one for follow counts, one for stream counts), find users whose stream count is at least twice their follow count. Show the username, artists_followed, and stream_count, ordered by username.

SQL

Expected Query

Expected Output

Exercise 4

Medium

Using two CTEs, find premium users whose stream count is at least three. Show the username and stream_count, ordered by stream_count descending.

SQL

Expected Query

Expected Output

Exercise 5

Hard

Using three CTEs, calculate each user's percentage of their country's total streams. The pipeline is: (1) total streams per user, (2) total streams per country, (3) join them to compute the share. Show username, country, user_streams, country_total, and pct (rounded to one decimal place). Order by country, then pct descending.

SQL

Expected Query

Expected Output