Data Analyst Interview Questions & Answers: Set 18, Energy & Utilities

SQL

1. 1.Find customers whose electricity usage this month is 3 times their usual average.

   Hide answer

   Answer: Compare each customer's current-month usage with their average over the previous 12 months, excluding the current month from the baseline. Assume monthly_usage(customer_id, usage_month, kwh), where usage_month is the first day of the month. Trap: if the current month is still in progress, compare a full month with full months, or scale the partial month. Also require some history (for example at least 6 months), so new customers are not flagged unfairly.

   WITH hist AS (
     SELECT customer_id, AVG(kwh) AS avg_kwh, COUNT(*) AS n_months
     FROM monthly_usage
     WHERE usage_month >= DATE_TRUNC('month', CURRENT_DATE)
                          - INTERVAL '12 months'
       AND usage_month <  DATE_TRUNC('month', CURRENT_DATE)
     GROUP BY customer_id
   )
   SELECT m.customer_id, m.kwh, ROUND(h.avg_kwh, 1) AS usual_avg
   FROM monthly_usage m
   JOIN hist h ON h.customer_id = m.customer_id
   WHERE m.usage_month = DATE_TRUNC('month', CURRENT_DATE)
     AND h.n_months >= 6
     AND m.kwh >= 3 * h.avg_kwh;

2. 2.Calculate total consumption per area by peak and off-peak hours.

   Hide answer

   Answer: Use conditional aggregation: tag each reading as peak or off-peak by its hour, then sum per area. Assume readings(meter_id, reading_ts, kwh) and meters(meter_id, area), with peak defined as 17:00 to 22:59 (use your utility's official peak window). In PostgreSQL you can also write SUM(kwh) FILTER (WHERE ...). Trap: check whether reading_ts marks the start or end of the interval, or a reading can land in the wrong hour.

   SELECT m.area,
          SUM(CASE WHEN EXTRACT(HOUR FROM r.reading_ts) BETWEEN 17 AND 22
                   THEN r.kwh ELSE 0 END) AS peak_kwh,
          SUM(CASE WHEN EXTRACT(HOUR FROM r.reading_ts) BETWEEN 17 AND 22
                   THEN 0 ELSE r.kwh END) AS offpeak_kwh,
          SUM(r.kwh) AS total_kwh
   FROM readings r
   JOIN meters m ON m.meter_id = r.meter_id
   WHERE r.reading_ts >= DATE '2026-09-01'
     AND r.reading_ts <  DATE '2026-10-01'
   GROUP BY m.area
   ORDER BY m.area;