Identify Date with Highest Total kWh Consumption Using Pandas
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
meter_readings
+----------+------------+---------------+---------+
| meter_id | timestamp | kwh_consumed | city |
+----------+------------+---------------+---------+
| 1001 | 2023-01-01 | 15.2 | Austin |
| 1001 | 2023-01-02 | 18.0 | Austin |
| 1002 | 2023-01-01 | 22.5 | Dallas |
| 1002 | 2023-01-02 | 20.1 | Dallas |
| 1003 | 2023-01-01 | 10.7 | Houston |
+----------+------------+---------------+---------+
##### Scenario
Analyst needs to summarize smart-meter readings for an operations report.
##### Question
Using pandas, aggregate total kwh_consumed for each date and return the date with the highest total consumption.
##### Hints
groupby on timestamp, sum, use idxmax or sort_values.
Overview: This question evaluates proficiency in pandas-based data manipulation and time-series aggregation, specifically the competency to aggregate and summarize meter readings to identify peak consumption dates.
Given the meter_readings table of smart-meter data, write an SQL query to aggregate the total kwh_consumed for each date (timestamp) across all meters, and return the single date with the highest total consumption along with that total.
Tables
meter_readings(meter_id INTEGER, timestamp DATE, kwh_consumed DECIMAL(10,1), city VARCHAR(50))
Hints
- GROUP BY timestamp and SUM kwh_consumed
- ORDER BY the summed value in descending order and LIMIT 1