Context

Operations teams often want a report with one row per week and separate columns for each region. This is a common pivot-style summary used for dashboards and weekly reviews.

Question

Explain how you would use SQL to create a pivot-style table that summarizes operational performance by region and week in PostgreSQL. Your answer should cover:

1. How to group records into weekly buckets
2. How to aggregate operational metrics such as total jobs or average completion time
3. How to turn region values into columns in the final result
4. What to do when some regions have no data for a given week

Scope Guidance

Keep the explanation practical. The interviewer expects you to describe the SQL approach clearly, mention PostgreSQL-friendly techniques, and explain trade-offs between manual pivoting and database-specific pivot tools.

Weekly Bucketing

To summarize by week, you first convert each event date into a consistent weekly bucket. In PostgreSQL, DATE_TRUNC('week', date_column) is commonly used to align all dates in the same week to the same starting timestamp.

SELECT DATE_TRUNC('week', completed_at) AS week_start
FROM operations;

Aggregation Before Pivoting

A pivot report is built on top of grouped metrics. You typically aggregate by week and region first using functions like COUNT, SUM, or AVG, then reshape the result into columns.

SELECT DATE_TRUNC('week', completed_at) AS week_start, region, COUNT(*) AS total_jobs
FROM operations
GROUP BY 1, 2;

Conditional Aggregation for Pivoting

PostgreSQL does not have a simple built-in PIVOT keyword like some other databases. A common approach is conditional aggregation, where each output column uses CASE WHEN region = '...' THEN metric END inside an aggregate.

SELECT
  DATE_TRUNC('week', completed_at) AS week_start,
  SUM(CASE WHEN region = 'North' THEN 1 ELSE 0 END) AS north_jobs,
  SUM(CASE WHEN region = 'South' THEN 1 ELSE 0 END) AS south_jobs
FROM operations
GROUP BY 1;

Handling Missing Region Data

If a region has no rows in a given week, the pivoted value may become NULL depending on the aggregation logic. Using COALESCE ensures the report shows 0 or another default value instead of a blank.

COALESCE(SUM(CASE WHEN region = 'West' THEN jobs END), 0) AS west_jobs

Static vs Dynamic Pivot

A static pivot works well when the list of regions is known and stable. If regions change often, you may need dynamic SQL or reporting-layer logic, because SQL column names must be known when the query is parsed.