Exercise: Aggregate Functions (COUNT, SUM, AVG)
Last Updated: May 12, 2026
For this exercise, we will work with below data:
Exercise 1
Count the total number of rows in the streams table. Return the result as total_streams.
Expected Query
Expected Output
Exercise 2
Compute the total of all amount values in the payments table. Alias the result as total_amount.
Expected Query
Expected Output
Exercise 3
Calculate the average duration_seconds across all tracks in the tracks table. Alias the result as avg_duration and do not round.
Expected Query
Expected Output
Exercise 4
Count how many distinct users appear in the streams table. Alias the result as active_users.
Expected Query
Expected Output
Exercise 5
From the streams table, return four aggregates in a single row: total stream rows (total_streams), rows with a non-NULL country (with_country), distinct non-NULL country values (distinct_countries), and the total duration_listened across all rows (total_seconds).
Expected Query
Expected Output
Exercise 6
Compute total revenue and total refunds from the payments table, restricted to rows where status is 'completed' or 'refunded' (excluding failed). Also return net revenue as total - refunds.
Expected Query
Expected Output
Exercise 7
From the payments table, return the count of refunded rows, the SUM of refund_amount across all rows, and the SUM of refund_amount only across rows where status is 'refunded'. Aliases: refunded_count, total_refund_field, refunded_status_total.