Structured Query Language Fundamentals and Aggregation Questions

This topic covers core Structured Query Language fundamentals for analytical querying and reporting. Candidates should be able to write correct, readable, and maintainable SELECT queries with filtering using WHERE, sorting with ORDER BY, grouping with GROUP BY, and group filtering with HAVING. They should apply aggregate functions such as COUNT, COUNT DISTINCT, SUM, AVG, MIN, and MAX and understand how NULL values affect results, how empty result sets behave, and when to use different counting approaches. The scope includes date and time filtering, basic cohort segmentation, and common time based comparisons used to compute metrics such as daily active users, average revenue per user, and period over period comparisons. Candidates are expected to use basic joins and join predicates including inner joins and left joins, write simple subqueries and conditional expressions, and perform common data transformation and cleansing patterns to prepare data for analysis. Finally, this topic assesses query readability and maintainability practices such as aliasing and formatting, plus awareness of elementary performance considerations including index usage and avoiding unnecessary full table scans for entry to mid level analytical tasks.

MediumTechnical

62 practiced

Using transactions(transaction_id INT, user_id INT, amount DECIMAL, occurred_at TIMESTAMP) and users(user_id INT, signup_date DATE), write SQL to compute ARPU (Average Revenue Per User) for the last 30 days defined as: total_amount_in_period / count_distinct_active_users_in_period. Return period_start, total_revenue, active_users, arpu. Ensure your query avoids division by zero and clarifies how to treat users with 0 revenue.

Sample Answer

Approach: aggregate transactions for the 30-day window, count distinct users who had transactions in that window (active users), and compute ARPU as total_revenue / active_users using a safe CASE to avoid division by zero. Note: this treats "active users" as users with at least one transaction in the period (users with 0 revenue are excluded). Below I also show an alternative to include signed-up users with no transactions.

sql

WITH window AS (
  SELECT
    date_trunc('day', now() - interval '30 days') AS period_start,
    now() AS period_end
),
tx AS (
  SELECT
    t.user_id,
    SUM(t.amount) AS total_revenue
  FROM transactions t
  JOIN window w ON t.occurred_at >= w.period_start AND t.occurred_at < w.period_end
  WHERE t.amount IS NOT NULL
  GROUP BY t.user_id
),
agg AS (
  SELECT
    (SELECT period_start FROM window) AS period_start,
    COALESCE(SUM(total_revenue), 0) AS total_revenue,
    COUNT(DISTINCT user_id) AS active_users
  FROM tx
)
SELECT
  period_start,
  total_revenue,
  active_users,
  CASE WHEN active_users = 0 THEN 0.00 ELSE total_revenue::numeric / active_users END AS arpu
FROM agg;

Notes and considerations:- Division-safe via CASE; returns 0 when no active users.- Users with zero revenue are excluded because "active" = had a transaction. If you must include users who signed up but had 0 revenue, join users (signup_date <= period_end) and use COUNT(DISTINCT u.user_id) in denominator instead—decide based on business definition.- Handle refunds/negative amounts per business rules (may want SUM(GREATEST(amount,0)) or separate net revenue).- Watch timezones for occurred_at and now().

EasyTechnical

54 practiced

Explain the differences between COUNT(*), COUNT(column) and COUNT(DISTINCT column) in SQL. For each variant describe how NULL values are treated, what exactly is being counted, and provide a short example (3-5 rows) showing differing outputs. Also discuss performance and when you'd prefer one form over another in reporting queries.

Sample Answer

COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column; COUNT(DISTINCT column) counts distinct non-NULL values.

Null handling / what counted:- COUNT(*): includes every row regardless of NULLs.- COUNT(column): ignores rows where column IS NULL; counts only non-NULL entries.- COUNT(DISTINCT column): ignores NULLs and deduplicates values.

Example table orders (3 rows):

sql

-- id | customer_id | status
1 | 10 | 'shipped'
2 | NULL | 'pending'
3 | 10 | NULL

Outputs:- SELECT COUNT(*) FROM orders; -> 3- SELECT COUNT(customer_id) FROM orders; -> 2 -- ids: 10, NULL ignored- SELECT COUNT(DISTINCT customer_id) FROM orders; -> 1 -- distinct non-NULL: 10

Performance and when to use:- COUNT(*) is fastest for total rows; databases optimize it (use table stats or full scan).- COUNT(column) is similar cost to COUNT(*) if column is not nullable or covered by index; otherwise may read column values.- COUNT(DISTINCT) is more expensive (sorting/hashing, may use temp space). Use DISTINCT when you need unique counts (e.g., unique users). For large datasets consider approximate distinct (HyperLogLog) or pre-aggregated tables for performance in BI reports.

MediumTechnical

50 practiced

Write a single SQL query to compute month, revenue, and month_over_month_pct_change for the last 12 full months using transactions(created_at TIMESTAMP, amount DECIMAL). Use window functions or self-join to compute the previous month's revenue and calculate percent change. Make sure your query handles months with zero revenue and avoids division by zero.

Sample Answer

Approach: generate a complete list of the last 12 full months (even months with zero revenue) using generate_series, aggregate transactions by month, then use a window LAG() to get previous month's revenue and compute percent change while avoiding division by zero.

sql

WITH months AS (
  -- last 12 full months: from 12 months ago up to last month
  SELECT generate_series(
           date_trunc('month', current_date) - interval '12 months',
           date_trunc('month', current_date) - interval '1 month',
           interval '1 month'
         )::date AS month_start
),
monthly_revenue AS (
  SELECT
    m.month_start,
    COALESCE(SUM(t.amount), 0)::numeric(18,2) AS revenue
  FROM months m
  LEFT JOIN transactions t
    ON t.created_at >= m.month_start
   AND t.created_at <  (m.month_start + interval '1 month')
  GROUP BY m.month_start
)
SELECT
  to_char(month_start, 'YYYY-MM') AS month,
  revenue,
  -- month_over_month_pct_change: NULL when previous revenue = 0 (to avoid division by zero)
  CASE
    WHEN prev_revenue IS NULL THEN NULL
    WHEN prev_revenue = 0 AND revenue = 0 THEN 0.0
    WHEN prev_revenue = 0 THEN NULL
    ELSE ROUND((revenue - prev_revenue) / prev_revenue * 100.0, 2)
  END AS month_over_month_pct_change
FROM (
  SELECT
    month_start,
    revenue,
    LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue
  FROM monthly_revenue
) s
ORDER BY month_start;

Key points:- generate_series ensures months with zero transactions appear.- Aggregation uses LEFT JOIN and COALESCE to treat missing months as zero revenue.- LAG() retrieves previous month revenue for percent-change calculation.- Division by zero avoided with CASE and NULLIF logic; returns NULL when prior revenue = 0 (except when both are zero, returns 0%).- Format month as YYYY-MM and round percent to 2 decimals.

Edge cases:- If you prefer to show +infinite or 100% when previous revenue = 0 and current > 0, change the CASE accordingly.- If transactions timezone matters, ensure created_at is normalized to the desired timezone before grouping.

MediumTechnical

58 practiced

Write a SQL query to flag transactions where amount > 3 * average_transaction_amount_for_that_user_over_the_previous_30_days (excluding the current transaction). Use transactions(transaction_id, user_id, amount, occurred_at). Return transaction_id, user_id, amount, avg_30d, is_anomaly boolean. Provide a solution using window functions or correlated subqueries and explain performance trade-offs.

Sample Answer

Approach: compute, for each transaction, the average amount for the same user over the prior 30 days (excluding the current row). Two common options: a correlated subquery (simple, readable) or window functions with RANGE/ROWS or a self-join+window for portability. Below are both implementations and trade-offs.

Correlated subquery (portable, clear):

sql

SELECT
  t.transaction_id,
  t.user_id,
  t.amount,
  (
    SELECT AVG(t2.amount)
    FROM transactions t2
    WHERE t2.user_id = t.user_id
      AND t2.occurred_at >= t.occurred_at - INTERVAL '30 days'
      AND t2.occurred_at < t.occurred_at
  ) AS avg_30d,
  CASE
    WHEN t.amount > 3 * COALESCE((
      SELECT AVG(t2.amount)
      FROM transactions t2
      WHERE t2.user_id = t.user_id
        AND t2.occurred_at >= t.occurred_at - INTERVAL '30 days'
        AND t2.occurred_at < t.occurred_at
    ), 0) THEN TRUE
    ELSE FALSE
  END AS is_anomaly
FROM transactions t;

Window-function approach (more performant at scale if indexed and optimized; avoids repeated scans by pre-aggregating per user/time):

sql

WITH ranked AS (
  SELECT
    transaction_id,
    user_id,
    amount,
    occurred_at,
    AVG(amount) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at
      RANGE BETWEEN INTERVAL '30 days' PRECEDING AND INTERVAL '1 microsecond' PRECEDING
    ) AS avg_30d
  FROM transactions
)
SELECT
  transaction_id, user_id, amount, avg_30d,
  (amount > 3 * COALESCE(avg_30d, 0)) AS is_anomaly
FROM ranked;

Key points & trade-offs:- Correlated subquery: simpler but can be expensive (scans per row) unless the DB can optimize; good for smaller datasets or when proper indexes on (user_id, occurred_at) exist.- Window + RANGE: computes rolling average in one pass, more efficient for large datasets; however RANGE with time intervals is not supported in every DB (Postgres supports it with appropriate types). Some engines fallback to less efficient strategies.- Alternative: pre-aggregate by day per user and then join for 30-day rolling average to reduce row count.Performance tips:- Index on (user_id, occurred_at) is essential.- Consider materialized views or daily aggregates for dashboards to avoid expensive real-time scans.Edge cases:- No prior transactions → avg_30d NULL; use COALESCE or treat as non-anomalous depending on business rule.- Outliers in historical data — may want median or trimmed mean instead of AVG.

HardTechnical

46 practiced

Write a SQL query to compute daily conversion_rate = conversions / active_users for each day using two aggregated tables or subqueries (daily_conversions and daily_active_users). Safeguard against division by zero and NULLs so the rate is 0 when active_users = 0 and NULL only when both numerator and denominator are NULL. Explain your use of COALESCE and NULLIF.

Sample Answer

Approach: full-outer-join the two aggregated sources by day so we include days present in either table, then compute conversion_rate with logic:- If both conversions and active_users are NULL → rate is NULL.- Else if active_users is zero or NULL → treat as zero denominator and return 0.- Otherwise divide conversions by active_users (safe from divide-by-zero by using NULLIF).

SQL:

sql

SELECT
  day,
  conversions,
  active_users,
  CASE
    WHEN conversions IS NULL AND active_users IS NULL THEN NULL
    WHEN COALESCE(active_users, 0) = 0 THEN 0.0
    ELSE conversions::numeric / NULLIF(active_users, 0)
  END AS conversion_rate
FROM (
  SELECT
    COALESCE(c.day, a.day) AS day,
    c.conversions,
    a.active_users
  FROM daily_conversions c
  FULL OUTER JOIN daily_active_users a
    ON c.day = a.day
) t
ORDER BY day;

Explanation of COALESCE and NULLIF:- COALESCE(active_users, 0) returns 0 when active_users is NULL; used to treat NULL as zero for the "zero-denominator => 0 rate" rule and to allow the conditional check COALESCE(...)=0.- NULLIF(active_users, 0) returns NULL when active_users = 0; used in the actual division to avoid a divide-by-zero error — dividing by NULL yields NULL, but our CASE prevents that path because we check zero earlier. The combination ensures safe arithmetic while preserving the business rule that rate is NULL only when both inputs are NULL.

Unlock Full Question Bank

Get access to hundreds of Structured Query Language Fundamentals and Aggregation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.