Write a query to find customers who upgraded their subscription plan more than once in the last year.

Answer

Count subscription upgrade events per customer within the last year, then keep customers with more than one.

Solution

SELECT customer_id, COUNT(*) AS upgrade_count
FROM SubscriptionEvents
WHERE event_type = 'upgrade'
  AND event_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY customer_id
HAVING COUNT(*) > 1;

Step by Step

1. WHERE event_type = 'upgrade' filters the event log down to just upgrade events, and the date filter restricts to the trailing 12 months.
2. GROUP BY customer_id collapses each customer's remaining upgrade events into one row.
3. COUNT(*) tallies how many upgrade events that customer had in the window, and HAVING COUNT(*) > 1 keeps only those with more than one.

Alternative: Using Plan-Level Change Detection Instead of an Event Log

WITH plan_changes AS (
  SELECT customer_id, plan_id, effective_date,
         LAG(plan_id) OVER (PARTITION BY customer_id ORDER BY effective_date) AS prev_plan_id
  FROM SubscriptionHistory
  WHERE effective_date >= CURRENT_DATE - INTERVAL '1 year'
)
SELECT customer_id, COUNT(*) AS upgrade_count
FROM plan_changes
WHERE plan_id > prev_plan_id  -- assumes higher plan_id = higher tier
GROUP BY customer_id
HAVING COUNT(*) > 1;

If no dedicated "event" table exists but a plan-history table does, comparing each row's plan to the previous one (via LAG()) can derive upgrades directly - useful to mention if the interviewer clarifies the schema differently than the question implies.

Key Exam Traps

• "Last year" is a rolling window, not a fixed calendar year - CURRENT_DATE - INTERVAL '1 year' means the trailing 365 days from today, not necessarily January-to-December of the current year. Confirm which interpretation the question intends.
• Upgrade vs any plan change: If the events table also logs downgrades or lateral plan switches under a generic "plan_change" type, filtering only on event_type = 'upgrade' is essential - counting all plan changes would answer a different question.
• HAVING vs WHERE: The row-level date/type filter belongs in WHERE (applied before grouping); the count threshold belongs in HAVING (applied after grouping) - mixing these up is a very common beginner mistake.