Fix dash dates and aggregate watch time
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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):
1) 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.
2) 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.
3) 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.
Overview: 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.
Read the full Capital One Data Scientist interview experience this question came from
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.
Tables
ad_view_logs(log_id INT, log_date_raw VARCHAR(10), time_slot VARCHAR(11), ad_id VARCHAR(10), watch_seconds INT)
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.
Tables
ad_view_logs(log_id INT, log_date_raw VARCHAR(10), time_slot VARCHAR(11), ad_id VARCHAR(10), watch_seconds INT)
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).