DISTINCT ON for unique per group in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select unique users by their country using DISTINCT ON.

PostgreSQL

SELECT DISTINCT ON ([1]) user_id, country FROM users ORDER BY country, user_id;

Drag options to blanks, or click blank then click option'

Auser_id

Bcountry

Cuser_name

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column not in ORDER BY with DISTINCT ON.

Selecting DISTINCT ON without specifying the column.

The DISTINCT ON (country) clause selects the first row for each unique country.

2fill in blank

medium

Complete the code to order users by signup date within each country.

PostgreSQL

SELECT DISTINCT ON (country) user_id, country, signup_date FROM users ORDER BY country, [1] DESC;

Drag options to blanks, or click blank then click option'

Aemail

Buser_id

Ccountry

Dsignup_date

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by a column not related to the selection.

Not ordering by the same column as DISTINCT ON.

Ordering by signup_date DESC ensures the newest user per country is selected.

3fill in blank

hard

Fix the error in the query to correctly select the first order per customer.

PostgreSQL

SELECT DISTINCT ON (customer_id) order_id, customer_id, order_date FROM orders ORDER BY [1];

Drag options to blanks, or click blank then click option'

Acustomer_id

Border_id

Corder_date

Dorder_status

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering only by order_date without customer_id.

Using columns in ORDER BY not matching DISTINCT ON.

The ORDER BY must start with the same column(s) as DISTINCT ON, here customer_id.

4fill in blank

hard

Fill both blanks to select the earliest order per customer sorted by order date.

PostgreSQL

SELECT DISTINCT ON ([1]) order_id, [2], order_date FROM orders ORDER BY customer_id, order_date;

Drag options to blanks, or click blank then click option'

Acustomer_id

Border_id

Corder_date

Dcustomer_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using order_date in DISTINCT ON instead of customer_id.

Selecting customer_name instead of order_id.

DISTINCT ON (customer_id) groups by customer, and selecting order_id shows the order details.

5fill in blank

hard

Fill all three blanks to select the latest login per user with their email.

PostgreSQL

SELECT DISTINCT ON ([1]) [2], email, login_time FROM logins ORDER BY [3] DESC;

Drag options to blanks, or click blank then click option'

Auser_id

Clogin_time

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by email instead of login_time.

Using email in DISTINCT ON instead of user_id.

DISTINCT ON groups by user_id, selecting email and ordering by login_time DESC picks the latest login per user.