Convert integer dates to quarters
Company: Point72
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Given an 8-digit integer date_key in YYYYMMDD (e.g., 20240331), write: (a) a SQL expression and (b) a Python function that convert it to a quarter label 'YYYY-Qn' using the Gregorian calendar (Q1=Jan–Mar, Q2=Apr–Jun, Q3=Jul–Sep, Q4=Oct–Dec). Requirements:
- Validate the date (reject or return NULL for impossible values such as 20250230, month=00/13, day=00, or 20210229 in a non-leap year).
- Support an optional fiscal_start_month s (1–12). If s ≠ 1, compute fiscal year and fiscal quarter; for example, with s=4 (Apr), 20250331 -> '2024-Q4' and 20250401 -> '2025-Q1'.
- Treat inputs that are strings with leading/trailing whitespace and leading zeros appropriately.
- Show the exact outputs for: 20240229, 20230228, 20251231, 20250101, 20250331 with s=4, and an invalid 20251301.
Quick Answer: This question evaluates date manipulation, validation, and fiscal-calendar conversion skills in both SQL and Python, covering parsing of string and integer inputs, leap-year and invalid-date detection, and computation of fiscal year/quarter offsets in the Data Manipulation (SQL/Python) domain.