SQL
92 bites tagged SQL — interview questions with model answers, and 60-second explainers.
Purpose of an ORM like Sequelize
Maps rows to objects, gives a model-based API, handles associations, migrations, and parameterized queries across dialects. understanding ORM value and tradeoffs.
Database migrations with the Sequelize CLI
Migrations are version-controlled scripts with up/down so teams apply identical schema changes; use sequelize-cli to generate, edit with addColumn, then db:migrate. versioned, repeatable schema changes.
How do you choose between relational and NoSQL databases?
Relational gives schema, joins, and ACID for structured related data; document gives flexible schema and horizontal scale for varied or denormalized data. ability to match a data model to requirements.
Design a SQL upsert from a staging table
Define a stable key, use MERGE or INSERT ON CONFLICT, dedupe the staging set first, run in a transaction. knowledge of idempotent loads. a naive INSERT that duplicates or a delete-then-insert race.
Calculate MRR with SQL including annual plans
Sum monthly_price for subscriptions active this month, filter on start and end dates, and normalize annual plans by dividing annual price by 12. correct MRR definition and date filtering.
Calculating Daily Active Users in SQL
Need per-event user_id and timestamp and a clear active definition; count distinct user_id within the day in a fixed timezone. Metric definition plus dedup SQL. Counting rows or fuzzy date-boundary and timezone handling.
Building a conversion funnel in SQL
Count distinct users reaching each ordered step, compute step-over-step conversion; the biggest drop-off is the lowest consecutive ratio. Funnel SQL and drop-off reasoning. Comparing each step to the total, or counting events.
SQL for a three-step onboarding funnel
Anchor the 30-day signup cohort, count distinct users reaching each later step in timestamp order; conversion is each step over the prior. Funnel SQL with ordering correctness. Counting any occurrence regardless of order.
Design a database schema for a research participant panel
Your ability to normalize relational data without over-engineering. Propose a participants table for contact and demographics; a participation_history table with foreign keys; and a consent_log table.
How would you implement a last-touch attribution model for user signups?
Tests your ability to translate marketing concepts into warehouse SQL. A strong answer covers UTM/pageview events, sessionized tables, and a windowed join for the last touch within 30 days of signup.
Describe the SQL and data model for weekly cohort retention
Join users and activity, compute week offset per user, group by cohort and offset for retention. Anchoring activity to signup cohort. Counting active users globally without cohort anchoring.
What is the difference between WHERE and HAVING in SQL?
Tests SQL execution order and aggregation. A strong answer states WHERE filters rows before grouping, HAVING filters groups after aggregation, and gives an aggregate example that WHERE cannot evaluate.
Find users who never placed an order and explain JOIN choice
This tests SQL anti-joins and NULL semantics. A strong answer uses LEFT JOIN with IS NULL or NOT EXISTS, explains why NOT IN is risky with NULLs, and why NOT EXISTS is preferred. Red flag: using INNER JOIN or ignoring NULLs.
Describe 1NF, 2NF, 3NF, normalization's purpose, and its performance trade-off.
1NF atomic values; 2NF no partial dependencies; 3NF no transitive dependencies; prevents update anomalies but adds join overhead. Linking forms to anomaly prevention and join overhead. Jargon without linking to anomalies.
What is the difference between DDL and DML in SQL?
DDL shapes schema with CREATE or ALTER; DML handles row-level data with SELECT, INSERT, UPDATE, or DELETE. Your grasp of the schema-versus-data boundary. Labeling SELECT as DDL or insisting DDL never affects data.
What is the difference between primary, foreign, and unique keys?
This tests relational integrity basics. Answer: primary keys identify rows, foreign keys reference tables, and unique keys are alternate candidates. Red flag: saying unique keys are just for indexing or omitting a non-PK example like email.
The N+1 Query Problem
N+1 means fetching one record, then looping to query its relations one by one. It explodes latency in ORM code that looks innocent, turning a page load into hundreds of round-trips. The fix is eager loading, yet developers often miss it until production melts.
Database Joins: Nested, Hash, Sort-Merge
A join matches rows by trading memory for speed. Nested loops use indexes; hash joins load large sets into RAM; sort-merge streams sorted data. The optimizer hides its choice, so a missing index can force a disk-spilling hash join.
SQL JOIN: Match Rows Across Tables
A SQL JOIN matches rows across tables on a shared key to build one logical record. You use it when orders need customer names or posts need authors. The footgun is that INNER JOIN silently drops rows with missing keys, making data seem to vanish.
How SQL Queries Become Abstract Syntax Trees
An AST turns a flat SQL string into a tree of operations the database can reason about. The parser builds this tree before execution planning. Do not confuse it with the raw parse tree, which keeps punctuation and formatting the AST strips away.
DML: Insert, Update, and Delete
DML is the change-focused subset of database languages like SQL that adds, modifies, and removes data. It appears in every write to a table. The footgun is assuming SELECT belongs in DML; read-only querying is sometimes split out as DQL instead.
DDL: The Blueprint for Database Objects
DDL is the blueprint for your database. You reach for it when spinning up new tables, indexes, or user permissions, not when querying rows. The footgun is running DROP thinking you are deleting data, not vaporizing the entire table structure.
SQL or NoSQL for high-volume semi-structured event ingestion?
Choose NoSQL for schema-less landing; use SQL downstream for structured analytics. schema flexibility and write throughput for raw event ingestion. Picking SQL for raw clicks because ACID is needed.
Find customers who have not placed any orders
Tests SQL anti-join logic. Great answers show two paths: LEFT JOIN plus IS NULL on orders.customer_id, or NOT EXISTS, and mention NULL safety with NOT IN. Red flag: INNER JOIN with DISTINCT, which silently drops customers without orders.
Get SQL bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.