▶️ Query playground
Senior-level SQL. Great if your interview goes past the basics, window functions and 3-table joins are common differentiators.
Reading data is only half the job, sometimes you shape it. These modify the practice database, so hit ↺ Reset database in the playground to restore the original data anytime.
These don't need a query. Practice explaining each in one or two clear sentences, or tap 🎴 Flashcards to quiz yourself one card at a time.
IS NULL.resolved_at IS NULL, use IS NULL, never = NULL.A variable is just a named box that holds a value so you can reuse it instead of re-typing the same number or text. Change it in one place, and every query that uses it updates.
Good to know: this playground runs SQLite, which has no variable syntax, so you can't run the examples below here. But the database you'll use on the job (SQL Server, MySQL, or PostgreSQL) each does it a little differently, and naming that difference in an interview shows real-world depth:
-- SQL Server (T-SQL) DECLARE @min_tickets INT = 3; SELECT account_id, COUNT(*) AS n FROM tickets GROUP BY account_id HAVING COUNT(*) > @min_tickets; -- MySQL SET @min_tickets = 3; SELECT account_id, COUNT(*) AS n FROM tickets GROUP BY account_id HAVING COUNT(*) > @min_tickets; -- PostgreSQL (inside a function/DO block, or the psql shell) \set min_tickets 3 SELECT ... HAVING COUNT(*) > :min_tickets;
💡 Say this in the interview: "SQLite doesn't support variables, but in T-SQL I'd DECLARE @var, in MySQL I'd use a SET @var session variable, and in Postgres a variable inside a function or a psql \set."
-- Filter rows
SELECT col1, col2 FROM table
WHERE priority = 'High' AND status <> 'Closed';
-- Sort + limit
SELECT * FROM tickets
ORDER BY created_at DESC LIMIT 5;
-- Aggregate + group
SELECT status, COUNT(*) AS n
FROM tickets GROUP BY status;
-- HAVING filters AFTER grouping (WHERE filters BEFORE)
SELECT account_id, COUNT(*) AS n
FROM tickets GROUP BY account_id
HAVING COUNT(*) > 3;
-- JOINs
SELECT t.subject, a.name
FROM tickets t
JOIN accounts a ON a.id = t.account_id; -- INNER = matches only
-- LEFT JOIN keeps ALL left rows; unmatched right = NULL
-- NULL handling
WHERE resolved_at IS NULL -- not "= NULL"
COALESCE(resolved_at, 'open') -- default if NULL
-- Subquery
SELECT * FROM tickets
WHERE account_id IN
(SELECT id FROM accounts WHERE plan='Enterprise');
-- Dates (SQLite)
WHERE created_at >= date('now','-30 days');
julianday(resolved_at) - julianday(created_at) -- difference in days
-- Window functions (SQLite 3.25+)
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at DESC)
RANK() / DENSE_RANK() OVER (ORDER BY total DESC)
COUNT(*) OVER (ORDER BY created_at) -- running total
LAG(col) OVER (PARTITION BY x ORDER BY y) -- previous row's value
-- CTE (a named, reusable subquery)
WITH resolved AS (
SELECT account_id,
julianday(resolved_at) - julianday(created_at) AS days
FROM tickets WHERE resolved_at IS NOT NULL
)
SELECT account_id, AVG(days) FROM resolved GROUP BY account_id;
-- Changing data (Level 3)
CREATE TABLE notes (id INTEGER PRIMARY KEY, ticket_id INTEGER, body TEXT);
INSERT INTO notes (ticket_id, body) VALUES (101, 'Escalated');
ALTER TABLE contacts ADD COLUMN phone TEXT;
UPDATE tickets SET status = 'Resolved' WHERE id = 101; -- ALWAYS include WHERE
DELETE FROM notes WHERE id = 1; -- ALWAYS include WHERE