SQL for Data Analysis Questions

Using SQL as a tool for data analysis and reporting. Focuses on writing queries to extract metrics, perform aggregations, join disparate data sources, use subqueries and window functions for trends and rankings, and prepare data for dashboards and reports. Includes best practices for reproducible analytical queries, handling time series and date arithmetic, basic query optimization considerations for analytic workloads, and when to use SQL versus built in reporting tools in analytics platforms.

MediumTechnical

114 practiced

Write SQL to compute median and 90th percentile session durations from a sessions table(session_id, user_id, duration_seconds). Use percentile_cont or equivalent. Describe how percentile functions behave with ties and small samples, and alternatives if your DB doesn't support percentile functions.

Sample Answer

To compute median (50th percentile) and 90th percentile session durations using a SQL percentile function (Postgres style):

sql

-- Overall median and 90th percentile across all sessions (Postgres)
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median_seconds,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY duration_seconds) AS p90_seconds
FROM sessions;

If you want these metrics per day or per user:

sql

-- Per-user percentiles
SELECT
  user_id,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median_seconds,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY duration_seconds) AS p90_seconds
FROM sessions
GROUP BY user_id;

How percentile functions behave:- percentile_cont returns a continuous (interpolated) value at the requested quantile. If the quantile falls between ordered rows, it linearly interpolates between adjacent values.- With ties (many identical durations), interpolation still occurs; result may be exactly that tied value or an interpolated value depending on rank positions.- With very small samples (fewer rows than needed for exact position), interpolation may produce values between observed points; results may be unstable. For n=1, percentile_cont(0.5) returns that single value.

Alternatives if DB lacks percentile functions:- Use percentile_disc (discrete) to return an actual observed row value (no interpolation).- Emulate using window functions: compute row_number() over ordered durations, map rank to percentile and pick nearest row.- Approximate at scale: use t-digest/approx_percentile (BigQuery/Presto), histogram bins, or export to analytics tools (Python/R) for robust quantile estimation.- Example discrete emulation (ANSI SQL using row_number):

sql

WITH ranked AS (
  SELECT duration_seconds,
         ROW_NUMBER() OVER (ORDER BY duration_seconds) AS rn,
         COUNT(*) OVER () AS n
  FROM sessions
)
SELECT duration_seconds AS approx_median
FROM ranked
WHERE rn = CEIL(n * 0.5);

Recommendation for product reporting:- Use percentile_cont for smooth medians in dashboards when DB supports it.- For SLA or user-facing thresholds prefer percentile_disc or t-digest approximations to avoid surprising interpolated values.

HardTechnical

66 practiced

As PM, propose an SQL-side data governance plan to prevent PII leakage in analytics. Explain role-based access, row-level security, column masking, anonymization (hashing/tokenization) strategies, and how to enforce via views, policies, and audit logs.

Sample Answer

Goal: prevent PII leakage in analytics while preserving analytical utility. Deliverables: access model, RLS, column masking, anonymization strategies, enforcement via views/policies, and audit logging.

1) Roles & RBAC- Define roles: analyst_readonly, analyst_pii_access, data_engineer, app_service, admin.- Grant least privilege: analytics schemas read-only; only analyst_pii_access can see raw PII.Example:

sql

CREATE ROLE analyst_readonly;
GRANT USAGE ON SCHEMA analytics TO analyst_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_readonly;

2) Row-Level Security (RLS)- Apply RLS for tenant isolation or user-scoped rows.- Policy example (Postgres):

sql

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_policy ON orders USING (tenant_id = current_setting('app.tenant_id')::int);

3) Column Masking & Dynamic Data Masking- For sensitive columns (ssn, email), implement masking functions for low-privilege roles; full value for pii role.

sql

CREATE FUNCTION mask_ssn(ssn text) RETURNS text AS $$ SELECT '***-**-' || substring($1 from 8) $$ LANGUAGE sql;
CREATE VIEW orders_masked AS
SELECT id, customer_id,
       CASE WHEN has_role('analyst_pii_access') THEN ssn ELSE mask_ssn(ssn) END AS ssn,
       email_mask(email) AS email
FROM orders;
GRANT SELECT ON orders_masked TO analyst_readonly;

4) Anonymization (hashing/tokenization)- Use irreversible hashing (salted HMAC) for analytics where uniqueness needed but PII not required.- For reversible tokenization (re-identification by ops), use a secure token service (not DB).

sql

-- pseudo: store hashed_id = HMAC_SHA256(salt, email)

5) Enforcement via Views & Policies- Expose only views to analysts; revoke table access.- Automate grants in CI; enforce via DB policies and secrets-managed salts for hashing.- Periodic job to check schema drift and unauthorized grants.

6) Audit Logs & Monitoring- Enable DB-level audit logging for SELECT/GRANT/DROP and access to sensitive columns.- Ship logs to SIEM; alert on anomalous queries (bulk exports, off-hours access).- Quarterly access reviews; tie to IAM SSO groups and automated offboarding.

Metrics & Governance- KPIs: number of PII exposures (0), failed policy alerts, time to revoke access.- Run privacy impact assessments for new datasets; document data lineage.

Trade-offs- Masking balances utility vs. risk; hashing preserves joinability but is irreversible. Tokenization adds ops complexity.

This plan pairs SQL controls (RLS, views, masking) with infra (token service, secrets, audits) and governance (roles, reviews) to prevent PII leakage.

HardTechnical

70 practiced

Write SQL to compute the top 10 products that increased the most in revenue rank between week N-1 and week N. Use transactions(product_id, amount, occurred_at). Output product_id, rank_last_week, rank_this_week, rank_change.

Sample Answer

Approach: aggregate revenue per product for week N and week N-1, compute revenue ranks using window functions (RANK() by revenue DESC), join the two rank sets, compute rank_change = rank_last_week - rank_this_week (positive => moved up), then pick top 10 increases.

sql

-- Assume :week_start is the date (YYYY-MM-DD) of the first day of week N (inclusive).
WITH params AS (
  SELECT DATE('2025-01-13')::date AS week_start  -- replace with target week start
),
weeks AS (
  SELECT
    week_start,
    week_start - INTERVAL '7 days' AS prev_week_start,
    week_start + INTERVAL '7 days' AS next_week_start
  FROM params
),
tx AS (
  SELECT t.*
  FROM transactions t
  JOIN weeks w
    -- keep only transactions in either week N or week N-1
    ON t.occurred_at >= w.prev_week_start
    AND t.occurred_at < w.next_week_start
),
rev_by_week AS (
  SELECT
    product_id,
    CASE
      WHEN occurred_at >= w.week_start AND occurred_at < w.week_start + INTERVAL '7 days' THEN 'this'
      WHEN occurred_at >= w.prev_week_start AND occurred_at < w.week_start THEN 'last'
    END AS which_week,
    SUM(amount) AS revenue
  FROM tx
  CROSS JOIN weeks w
  WHERE (occurred_at >= w.week_start AND occurred_at < w.week_start + INTERVAL '7 days')
     OR (occurred_at >= w.prev_week_start AND occurred_at < w.week_start)
  GROUP BY product_id, which_week
),
ranked AS (
  -- ranks for this week
  SELECT product_id, revenue AS revenue_this,
         RANK() OVER (ORDER BY revenue DESC) AS rank_this
  FROM rev_by_week
  WHERE which_week = 'this'
),
ranked_last AS (
  -- ranks for last week
  SELECT product_id, revenue AS revenue_last,
         RANK() OVER (ORDER BY revenue DESC) AS rank_last
  FROM rev_by_week
  WHERE which_week = 'last'
)
SELECT
  COALESCE(r.product_id, rl.product_id) AS product_id,
  rl.rank_last,
  r.rank_this,
  (COALESCE(rl.rank_last, NULL) - COALESCE(r.rank_this, NULL)) AS rank_change
FROM ranked r
FULL OUTER JOIN ranked_last rl USING (product_id)
-- Treat missing rank_last as worst+1 (optional). Here we show NULLs; filter below.
WHERE r.rank_this IS NOT NULL AND rl.rank_last IS NOT NULL
ORDER BY (rl.rank_last - r.rank_this) DESC
LIMIT 10;

Key points:- RANK() orders by revenue DESC so rank 1 = highest revenue. Using RANK preserves tie semantics; use DENSE_RANK if you prefer no gaps.- rank_change = rank_last - rank_this: positive means improved (moved toward 1).- Edge cases: products appearing only one week (decide how to handle NULLs — you may treat missing last-week rank as max_rank+1), ties, zero revenue, timezone effects on occurred_at.- Complexity: single scan + window sorts; performance depends on transactions volume—index on occurred_at and product_id helps.

HardTechnical

71 practiced

Given an experiment assignment table assignments(user_id, experiment_id, variant, assigned_at) and events(user_id, occurred_at, event_name), write SQL to compute 7-day retention for treatment vs control. Describe how you'll handle users who cross over variants or are reassigned after events.

Sample Answer

Approach: use an intention-to-treat (ITT) primary analysis — assign each user to the first experiment assignment (by assigned_at) and measure whether they have any event within 7 days after that assignment. Also describe alternatives: per-protocol (only count users who stayed in same variant for 7 days) and time-varying exposure (use assignment as of event time) for sensitivity.

SQL (Postgres) — ITT by first assignment per user, 7-day retention = percent with at least one event in [assigned_at, assigned_at + 7):

sql

WITH first_assign AS (
  SELECT user_id,
         experiment_id,
         variant,
         MIN(assigned_at) AS assigned_at
  FROM assignments
  WHERE experiment_id = 123  -- restrict to experiment
  GROUP BY user_id, experiment_id, variant
),
events_in_window AS (
  SELECT f.user_id,
         f.variant,
         COUNT(e.event_name) FILTER (WHERE e.occurred_at >= f.assigned_at
                                      AND e.occurred_at < f.assigned_at + INTERVAL '7 days') AS events_7d
  FROM first_assign f
  LEFT JOIN events e ON e.user_id = f.user_id
  GROUP BY f.user_id, f.variant
),
agg AS (
  SELECT variant,
         COUNT(*) AS n_users,
         SUM(CASE WHEN events_7d > 0 THEN 1 ELSE 0 END) AS retained_7d
  FROM events_in_window
  GROUP BY variant
)
SELECT variant,
       n_users,
       retained_7d,
       ROUND(100.0 * retained_7d / NULLIF(n_users,0), 3) AS pct_retained
FROM agg;

Key points and handling crossovers/reassignments:- ITT: attribute users to their first assignment to preserve randomization and avoid bias from post-randomization behavior.- Per-protocol: filter users who never switched variant within the 7-day window (exclude crossovers) to estimate effect among compliers.- Time-varying exposure: for causal/time-sensitive analysis, join events to the assignment interval that covers the event timestamp (assignment windows per user) and treat exposure as dynamic.- Reassignments after events: if reassignment happens after the event, ITT still attributes by initial assignment. For safety, exclude users who had events before assignment (if possible) and consider washout windows.- Report counts, confidence intervals (use Wilson/binomial), and run sensitivity analyses to show robustness.

EasyTechnical

66 practiced

What is a Common Table Expression (CTE) in SQL and when should you use it? Provide a short example CTE that computes weekly new_users and explain readability and potential performance considerations.

Sample Answer

A Common Table Expression (CTE) is a named, temporary result set you define at the start of a query (with WITH) that you can reference like a table. Use CTEs to break complex queries into readable, reusable pieces, make stepwise logic explicit, and avoid repeating subqueries. For a Product Manager, CTEs make analytics queries easier to review with analysts and stakeholders.

Example: compute weekly new_users from a users table with created_at timestamps.

sql

WITH user_weeks AS (
  SELECT
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS new_users
  FROM users
  WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1
)
SELECT week_start, new_users
FROM user_weeks
ORDER BY week_start;

Readability: CTEs give semantic names (user_weeks) so the intent is clear to non-engineers and reviewers. They help split validation steps (filtering, aggregation) and can be chained.

Performance: Most databases inline simple CTEs (like subqueries) with no cost, but some treat CTEs as optimization barriers (materializing them), which can hurt performance on large datasets. For heavy queries, test execution plans and consider indexed temporary tables or pushing filters into the base query.

Unlock Full Question Bank

Get access to hundreds of SQL for Data Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.