You receive a dataset of customer transactions in a relational database and are asked to identify trends in churn. Describe the SQL queries you would write to: (1) join key tables, (2) create basic cohort metrics, and (3) check data quality issues before analysis. Be explicit about the types of joins, filters, and aggregations you would use.

Question

VirtualInterview.AI · Accepted Answer

I’d start by sketching the schema. Let’s say we have customers, subscriptions, and transactions. First, I’d sanity-check row counts and nulls:

SELECT COUNT(*) FROM customers; SELECT COUNT(*) FROM transactions; then for key fields: SELECT COUNT(*) FROM customers WHERE customer_id IS NULL. I’d look for duplicates with GROUP BY customer_id HAVING COUNT(*)\u003e1.

To join, I’d typically LEFT JOIN subscriptions to customers (keep all customers) and INNER JOIN transactions to subscriptions:

SELECT c.customer_id, s.subscription_id, t.txn_date, t.amount
FROM customers c
LEFT JOIN subscriptions s ON c.customer_id = s.customer_id
INNER JOIN transactions t ON s.subscription_id = t.subscription_id
WHERE t.txn_date BETWEEN '2024-01-01' AND '2024-12-31';

For cohorts, I’d bucket by signup month and check 3/6/12‑month activity:

WITH first_txn AS (
  SELECT customer_id, MIN(txn_date) AS first_date
  FROM transactions
  GROUP BY customer_id
), activity AS (
  SELECT customer_id, DATE_TRUNC('month', first_date) AS cohort_month,
         MAX(txn_date) AS last_txn
  FROM first_txn f
  JOIN transactions t USING (customer_id)
  GROUP BY customer_id, cohort_month
)
SELECT cohort_month,
       COUNT(*) AS customers,
       SUM(CASE WHEN last_txn \u003c CURRENT_DATE - INTERVAL '90 days' THEN 1 ELSE 0 END) AS churned
FROM activity
GROUP BY cohort_month;

Sample Answer

Keywords