Common query optimization patterns in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select only distinct customer IDs from the orders table.

PostgreSQL

SELECT [1] customer_id FROM orders;

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

AGROUP BY

BALL

CDISTINCT

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY without aggregation can be confusing here.

Using UNIQUE is not valid SQL syntax in PostgreSQL.

The DISTINCT keyword removes duplicate rows, returning unique customer IDs.

2fill in blank

medium

Complete the code to create an index on the email column of the users table to speed up searches.

PostgreSQL

CREATE INDEX [1] ON users(email);

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

Aidx_email

Bemail_index

Cindex_email

Dusers_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved words as index names.

Using spaces or special characters in the index name.

Index names often start with idx_ to indicate an index. idx_email is a clear, valid name.

3fill in blank

hard

Fix the error in the query that tries to filter orders with total greater than 100 but uses the wrong clause.

PostgreSQL

SELECT * FROM orders [1] total > 100;

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

AORDER BY

BHAVING

CGROUP BY

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using HAVING without GROUP BY causes errors.

Using ORDER BY or GROUP BY instead of WHERE for filtering.

The WHERE clause filters rows before grouping or aggregation. Use it to filter orders with total > 100.

4fill in blank

hard

Fill both blanks to write a query that uses a CTE to get top 5 products by sales and then selects their names.

PostgreSQL

WITH top_products AS (SELECT product_id, SUM(sales) [1] sales_total FROM sales_data GROUP BY product_id ORDER BY sales_total DESC [2] 5) SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM top_products);

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

AAS

BBY

CLIMIT

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using BY instead of AS for aliasing.

Using DESC instead of LIMIT to restrict rows.

Use AS to alias the sum as sales_total. Use LIMIT to restrict to top 5 rows.

5fill in blank

hard

Fill all three blanks to write a query that aggregates total sales per region, filters regions with sales over 10000, and orders results descending.

PostgreSQL

SELECT region, SUM(sales) [1] total_sales FROM sales GROUP BY [2] HAVING total_sales [3] 10000 ORDER BY total_sales DESC;

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

AAS

Bregion

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING to filter aggregated data.

Using < instead of > in the HAVING clause.

Alias the sum as total_sales using AS. Group by region. Filter with > to get regions with sales over 10000.