Sector file // sql
SQLDECODED.
Joins, indexes & queries
Easy
5 questionsWhat is the difference between a primary key and a foreign key?
DSA · 1.2k views · primary-key, foreign-key
What is the difference between a primary key and a unique constraint?
DSA · 1.1k views · constraints
What does DISTINCT do and what is its cost?
DSA · 859 views · distinct
How do you find duplicate rows in a table?
DSA · 694 views · duplicates, group-by
What is the difference between CHAR, VARCHAR, and TEXT?
DSA · 94 views · data-types
Medium
26 questionsTop-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?
DSA · 1.2k views · window-functions, rank, partition
Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?
System Design · 1.2k views · upsert, idempotency, constraints
Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?
System Design · 1.2k views · covering-index, bitmap-scan, vacuum
What is a subquery and what is a correlated subquery?
DSA · 1.2k views · subquery
How do you read an EXPLAIN query plan?
DSA · 1.1k views · explain
LATERAL JOIN — how do you fetch the top 3 orders per customer efficiently?
DSA · 1.1k views · lateral-join, top-n, postgres
What is a covering index?
DSA · 1.1k views · covering-index
How do you aggregate values into a single string?
DSA · 1.1k views · string-agg
Hard
11 questionsSerializable isolation pitfalls — write skew, retry storms, and when advisory locks beat SSI?
System Design · 1.1k views · isolation, serializable, write-skew
JSONB vs normalized columns vs EAV — how do you model flexible attributes without killing query performance?
System Design · 1.1k views · jsonb, data-modeling, schema-design
What is a LATERAL join?
DSA · 970 views · lateral
SKIP LOCKED for job queues — how do you build a concurrent queue without race conditions?
System Design · 857 views · skip-locked, queue, concurrency
How do you optimize a slow SQL query?
DSA · 816 views · query-optimization
Why does column order matter in a composite index?
DSA · 585 views · composite-index
pgvector cosine similarity index in 2025-26 — HNSW vs IVFFlat for semantic search at scale?
System Design · 465 views · pgvector, hnsw, cosine-search
What is a deadlock and how do you prevent it?
DSA · 366 views · deadlock
Seen a SQL question that is not here? Add it and help the next candidate.
Share a question