SELECT with JOIN

A multi-table join with aliases and filtering. Keeping track of aliases is where most people slow down.

Part 1 of 1

SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_value FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.created_at >= '2024-01-01' GROUP BY u.id, u.name, u.email ORDER BY lifetime_value DESC;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 30 WPM

▼

⏱️ Start typing...

Subquery

A correlated subquery inside a WHERE clause. The nested SELECT is the part that requires careful typing.

Part 1 of 1

SELECT p.id, p.name, p.price FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category_id = p.category_id) AND p.stock_count > 0 ORDER BY p.price ASC;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 30 WPM

▼

⏱️ Start typing...

CREATE TABLE

A table definition with column types, constraints, and a foreign key. DDL syntax trips up even experienced people.

Part 1 of 1

CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL DEFAULT 1, unit_price NUMERIC(10, 2) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 25 WPM

▼

⏱️ Start typing...

INSERT with Values

A multi-row INSERT with ON CONFLICT handling. The RETURNING clause is easy to forget.

Part 1 of 1

INSERT INTO users (name, email, role, created_at) VALUES ('Alice Chen', '[email protected]', 'admin', NOW()), ('Bob Torres', '[email protected]', 'user', NOW()) ON CONFLICT (email) DO NOTHING RETURNING id, email;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 28 WPM

▼

⏱️ Start typing...

UPDATE with WHERE

An UPDATE that joins to another table before applying changes. Using a FROM clause makes this PostgreSQL-specific.

Part 1 of 1

UPDATE orders SET status = 'shipped', shipped_at = NOW(), tracking_code = shipments.tracking_code FROM shipments WHERE shipments.order_id = orders.id AND shipments.processed = true AND orders.status = 'processing';

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 28 WPM

▼

⏱️ Start typing...

GROUP BY with HAVING

Aggregation with a HAVING filter. The difference between WHERE and HAVING is the concept; typing it cleanly is the challenge.

Part 1 of 1

SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price, MAX(price) AS max_price FROM products WHERE is_active = true GROUP BY category_id HAVING COUNT(*) >= 5 AND AVG(price) < 500 ORDER BY avg_price DESC;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 30 WPM

▼

⏱️ Start typing...

Window Function

ROW_NUMBER and a running total using OVER/PARTITION BY. Window functions have a very specific syntax to get right.

Part 1 of 1

SELECT id, customer_id, total, created_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS order_rank, SUM(total) OVER (PARTITION BY customer_id) AS customer_total FROM orders WHERE status = 'completed';

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 25 WPM

▼

⏱️ Start typing...

Common Table Expression (CTE)

A WITH clause CTE followed by a query that uses it. CTEs make complex queries readable but require more to type.

Part 1 of 1

WITH ranked_products AS (SELECT id, name, category_id, price, RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank FROM products WHERE is_active = true) SELECT name, price, price_rank FROM ranked_products WHERE price_rank <= 3 ORDER BY category_id, price_rank;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 25 WPM

▼

⏱️ Start typing...

CREATE INDEX

A partial index with a composite key. Index definitions seem short but the syntax needs to be exact.

Part 1 of 1

CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders (user_id, status, created_at DESC) WHERE status IN ('pending', 'processing') AND deleted_at IS NULL;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 28 WPM

▼

⏱️ Start typing...

Stored Function

A PostgreSQL function with parameters and return type. The $$ delimiter and LANGUAGE declaration are easy to fumble.

Part 1 of 1

CREATE OR REPLACE FUNCTION get_user_stats(p_user_id INTEGER) RETURNS TABLE(order_count BIGINT, total_spent NUMERIC) LANGUAGE sql STABLE AS $$ SELECT COUNT(*), COALESCE(SUM(total), 0) FROM orders WHERE user_id = p_user_id AND status = 'completed'; $$;

WPM 0

Accuracy 100%

Progress 0%

Streak 0 🔥

Speed Target: 22 WPM

▼

⏱️ Start typing...

Please Allow Ads to Continue

How to disable your ad blocker:

Check DNS / Router Settings

SQL Practice

SELECT with JOIN

Subquery

CREATE TABLE

INSERT with Values

UPDATE with WHERE

GROUP BY with HAVING

Window Function

Common Table Expression (CTE)

CREATE INDEX

Stored Function