Convert Dates to Calendar Quarter Labels in SQL/Python
Company: Point72
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Transactions
+----+-----------+
| id | trans_dt |
+----+-----------+
| 1 | 20230115 |
| 2 | 20230402 |
| 3 | 20230930 |
| 4 | 20231201 |
+----+-----------+
##### Scenario
Financial reporting needs quarterly sales aggregation from raw numeric dates.
##### Question
Given a table of transactions where the date is stored as an 8-digit integer (YYYYMMDD), write SQL and/or Python code that converts each date into its calendar quarter label (e.g., 20230215 → 2023_Q
1).
##### Hints
Convert to date type, extract month, map month to quarter with CASE or arithmetic.
Overview: This question evaluates a candidate's ability to perform date/time data transformation and normalization in SQL or Python, focusing on converting raw numeric dates into standardized quarter labels for aggregation and reporting.
Given transaction dates stored as 8-digit integers in `YYYYMMDD` format, derive each transaction's calendar quarter label.
Use the `Transactions` table. Return one row per transaction with these columns:
- `id`
- `trans_dt`: the original numeric date
- `quarter_label`: a string in the format `YYYY_Qn`, such as `2023_Q1`
Order the result by `id`.
Tables
Transactions(id INTEGER, trans_dt INTEGER)
Hints
- Use integer division to extract the year and month from YYYYMMDD.
- The quarter is 1 + ((month - 1) / 3).