Coding questions · Capital One
Fix dash dates and aggregate watch time
This question evaluates data-cleaning and aggregation competencies, focusing on handling nonstandard missing-date markers, forward-filling in spreadsheets, correct date-range filtering, pivot-table summarization, and concise pandas grouping and aggregation.
You receive a CSV of ad viewing logs where the date column repeats only on the first row of each block and subsequent rows use a single dash '-' to indicate 'same as above'. Columns: date (YYYY-MM-DD or '-'), time_slot (e.g., '08:00-09:00'), ad_id, watch_seconds (INT). Tiny excerpt:
+------------+-----------+-------+---------------+
| date | time_slot | ad_id | watch_seconds |
+------------+-----------+-------+---------------+
| 2008-01-01 | 08:00-09:00 | A1 | 120 |
| - | 08:00-09:00 | A2 | 90 |
| - | 09:00-10:00 | A1 | 150 |
| 2008-01-08 | 08:00-09:00 | A1 | 80 |
+------------+-----------+-------+---------------+
Task A (Excel):
- Provide the exact Excel formula to forward-fill the date in cell A2 and down the column, replacing '-' with the most recent actual date above, without using VBA or Power Query.
- Then, using only worksheet functions, compute the time_slot with the largest total watch_seconds for the week 2008-01-01 to 2008-01-07 inclusive. Give the precise formulas (e.g., SUMIFS-based) and explain how you ensure the date filter is correct and dash rows are included after forward-fill.
- Provide a pivot-table approach that yields the same answer and state the two field placements and the exact date filter you would set.
Task B (pandas alternative): Write concise pandas code (no more than 5 lines) to: read the CSV; forward-fill the date column; filter to 2008-01-01 through 2008-01-07; group by time_slot; compute total watch_seconds; and return the single time_slot with the maximum total. Be explicit about handling '-' values.
Forward-fill dashed dates in ad viewing logs
You are given an ad viewing log where the date is only stored on the first row of each block, and subsequent rows in the block use a single dash '-' to mean 'same as the most recent date above'. The table ad_view_logs stores the raw import from the CSV. Write a SQL query that returns all columns plus an extra column filled_date of type DATE where each '-' is replaced with the most recent non-dash date above it, based on log_id ordering.
Table ad_view_logs
| column | type | constraints |
|---|---|---|
| log_id | INT | PRIMARY KEY, NOT NULL |
| log_date_raw | VARCHAR(10) | NOT NULL |
| time_slot | VARCHAR(11) | NOT NULL |
| ad_id | VARCHAR(10) | NOT NULL |
| watch_seconds | INT | NOT NULL |
Sample data:
| ad_id | log_date_raw | log_id | time_slot | watch_seconds |
|---|---|---|---|---|
| A1 | 2008-01-01 | 1 | 08:00-09:00 | 120 |
| A2 | - | 2 | 08:00-09:00 | 90 |
| A1 | - | 3 | 09:00-10:00 | 150 |
| A1 | 2008-01-02 | 4 | 08:00-09:00 | 60 |
| A3 | - | 5 | 09:00-10:00 | 210 |
| A1 | 2008-01-05 | 6 | 08:00-09:00 | 80 |
| A2 | - | 7 | 10:00-11:00 | 40 |
| A1 | 2008-01-08 | 8 | 08:00-09:00 | 80 |
| A2 | - | 9 | 09:00-10:00 | 70 |
| A3 | 2008-01-09 | 10 | 09:00-10:00 | 110 |
Expected output
All rows from ad_view_logs with an additional filled_date column where each '-' in log_date_raw has been replaced by the most recent non-dash date above it (based on log_id), cast to DATE.
| ad_id | filled_date | log_date_raw | log_id | time_slot | watch_seconds |
|---|---|---|---|---|---|
| A1 | 2008-01-01 | 2008-01-01 | 1 | 08:00-09:00 | 120 |
| A2 | 2008-01-01 | - | 2 | 08:00-09:00 | 90 |
| A1 | 2008-01-01 | - | 3 | 09:00-10:00 | 150 |
| A1 | 2008-01-02 | 2008-01-02 | 4 | 08:00-09:00 | 60 |
| A3 | 2008-01-02 | - | 5 | 09:00-10:00 | 210 |
| A1 | 2008-01-05 | 2008-01-05 | 6 | 08:00-09:00 | 80 |
| A2 | 2008-01-05 | - | 7 | 10:00-11:00 | 40 |
| A1 | 2008-01-08 | 2008-01-08 | 8 | 08:00-09:00 | 80 |
| A2 | 2008-01-08 | - | 9 | 09:00-10:00 | 70 |
| A3 | 2008-01-09 | 2008-01-09 | 10 | 09:00-10:00 | 110 |
Hints
- Use a window function ordered by log_id to propagate the last non-dash date downward.
- Combine CASE with MAX(...) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) to forward-fill.
Find the time slot with maximum watch time over a given week
Using the same ad_view_logs table where log_date_raw is either a date in 'YYYY-MM-DD' format or '-' meaning 'same as the most recent date above', write a SQL query that: (1) forward-fills the dates as in Question 1 to produce a filled_date of type DATE; (2) filters to rows where filled_date is between '2008-01-01' and '2008-01-07' inclusive; (3) groups by time_slot and computes total watch_seconds per time_slot; and (4) returns the time_slot(s) with the largest total watch_seconds in that date range, along with the total. If multiple time slots tie for the maximum total, return all of them.
Table ad_view_logs
| column | type | constraints |
|---|---|---|
| log_id | INT | PRIMARY KEY, NOT NULL |
| log_date_raw | VARCHAR(10) | NOT NULL |
| time_slot | VARCHAR(11) | NOT NULL |
| ad_id | VARCHAR(10) | NOT NULL |
| watch_seconds | INT | NOT NULL |
Sample data:
| ad_id | log_date_raw | log_id | time_slot | watch_seconds |
|---|---|---|---|---|
| A1 | 2008-01-01 | 1 | 08:00-09:00 | 120 |
| A2 | - | 2 | 08:00-09:00 | 90 |
| A1 | - | 3 | 09:00-10:00 | 150 |
| A1 | 2008-01-02 | 4 | 08:00-09:00 | 60 |
| A3 | - | 5 | 09:00-10:00 | 210 |
| A1 | 2008-01-05 | 6 | 08:00-09:00 | 80 |
| A2 | - | 7 | 10:00-11:00 | 40 |
| A1 | 2008-01-08 | 8 | 08:00-09:00 | 80 |
| A2 | - | 9 | 09:00-10:00 | 70 |
| A3 | 2008-01-09 | 10 | 09:00-10:00 | 110 |
Expected output
For the date range from 2008-01-01 to 2008-01-07 inclusive (after forward-filling dates), return the time_slot(s) with the highest total watch_seconds and their totals. On the sample data, there is a unique maximum.
| time_slot | total_watch_seconds |
|---|---|
| 09:00-10:00 | 360 |
Hints
- Reuse the forward-fill logic from Question 1 in a CTE or subquery to create a filled_date column.
- After aggregating by time_slot, use a window function like RANK() ordered by total watch_seconds descending to pick the maximum group(s).