SQL for Data Analysis

Almost every data science job starts with SQL, and almost every data science interview has a SQL round. Not because SQL is hard to write, but because it is easy to write SQL that runs without error and returns the wrong number. A join that fans out and triples revenue, a NOT IN that returns zero rows because of one NULL, a running total that jumps in steps because of the default window frame — these are the mistakes that get an analysis retracted a week after the deck went to leadership.

This subject teaches analytical SQL the way it is used in practice: on an event log, a users table and an orders table, with the questions a product analyst or data scientist actually gets asked. Every snippet is PostgreSQL and runs against the schema defined in the first section. Physical storage, B-trees and index selection are covered in the Database Indexing subject; here we only build the mindset an analyst needs to avoid writing pathological queries.

Interviewers use SQL to test three things: do you know the semantics precisely (evaluation order, NULLs, join cardinality), can you decompose a business question into set operations (cohorts, funnels, sessions), and can you spot when a number is wrong. Those are exactly the three threads of this subject.

The Schema We Will Use

CREATE TABLE users (
  user_id     BIGINT PRIMARY KEY,
  signup_date DATE NOT NULL,
  country     TEXT,                    -- may be NULL (unknown)
  plan        TEXT                     -- 'free' | 'pro' | NULL
);

CREATE TABLE events (
  event_id    BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(user_id),
  event_type  TEXT NOT NULL,           -- 'view' | 'add_to_cart' | 'checkout' | 'purchase'
  event_ts    TIMESTAMPTZ NOT NULL
);

CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(user_id),
  order_ts    TIMESTAMPTZ NOT NULL,
  amount      NUMERIC(10,2) NOT NULL,
  status      TEXT NOT NULL            -- 'paid' | 'refunded' | 'cancelled'
);

Cardinalities matter for everything that follows: one user has many events and many orders; events and orders are not related to each other except through user_id. Keep this picture in mind whenever you join.

users (1) ──< events (N)
users (1) ──< orders (M)