Find Payroll Bookings in the Latest Active Month by Country
RipplingData Analyst · Technical Screen
Last updated
Tests PostgreSQL date manipulation, filtering, and aggregation by determining the most recent month with active payroll bookings and summarizing totals by country.
Problem Statement
At Rippling, payroll runs are processed across thousands of businesses worldwide. Finance needs a report showing the distribution of payroll bookings across different countries for the most recent active month.
Write a PostgreSQL query to identify the latest calendar month (based on booking_date) that contains at least one successful booking (status = 'completed'), and return the aggregated booking metrics for each company headquarters country (hq_country) during that specific month.
Input Schema
companies
| Column | Type | Description |
|---|---|---|
company_id | INT | Unique identifier for the company |
company_name | VARCHAR | Name of the company |
hq_country | VARCHAR | Headquarters country (ISO 2-letter code or country name) |
payroll_bookings
| Column | Type | Description |
|---|---|---|
booking_id | INT | Unique identifier for the payroll booking |
company_id | INT | Foreign key referencing companies(company_id) |
booking_date | DATE | Date the payroll was booked |
booking_amount | NUMERIC(12, 2) | Gross payroll amount booked |
status | VARCHAR | Booking status ('completed', 'failed', 'pending') |
Output Format
Return the following columns:
hq_country: The headquarters country.total_amount: Total sum ofbooking_amountfor completed bookings in that month, rounded to 2 decimal places.booking_count: Total number of completed bookings in that month.
Order the results by total_amount descending, then by hq_country ascending.
Example
companies
| company_id | company_name | hq_country |
|---|---|---|
| 1 | Acme Corp | US |
| 2 | Beta Ltd | UK |
| 3 | Gamma GmbH | DE |
payroll_bookings
| booking_id | company_id | booking_date | booking_amount | status |
|---|---|---|---|---|
| 101 | 1 | 2024-02-15 | 10000.00 | completed |
| 102 | 2 | 2024-02-28 | 15000.50 | completed |
| 103 | 1 | 2024-03-05 | 12000.00 | completed |
| 104 | 2 | 2024-03-12 | 8000.00 | failed |
| 105 | 3 | 2024-03-20 | 5000.25 | completed |
| 106 | 1 | 2024-04-02 | 4000.00 | failed |
Explanation
The latest date with a 'completed' booking is in March 2024 (2024-03-20). The April booking failed, so April is not an active month. Looking only at completed bookings in March 2024:
US(Company 1): 12000.00 (1 booking)DE(Company 3): 5000.25 (1 booking)UKhas no completed bookings in March.
Output
| hq_country | total_amount | booking_count |
|---|---|---|
| US | 12000.00 | 1 |
| DE | 5000.25 | 1 |
Hints
Hint 1
Use DATE_TRUNC('month', booking_date) to truncate dates to the start of each calendar month.
Hint 2
Find the maximum truncated month among records where status = 'completed' using a subquery or Common Table Expression (CTE).
Hint 3
Join companies and payroll_bookings, filter by both the target month and status = 'completed', and use GROUP BY hq_country with proper ORDER BY logic.
Follow-up questions
- How would you optimize this query if `payroll_bookings` contains over 100 million rows?
- How would you extend this query to also return the month-over-month growth rate compared to the immediately preceding month for each country?
- If `booking_date` were a UTC timestamp and countries needed reporting in their local time zones, how would you adjust the date truncation logic?
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.