Task
You are given database telemetry for a customer’s Blue Yonder environment during a holiday peak window. Write a PostgreSQL query that returns the hourly periods where database latency spiked sharply, along with the workload and infrastructure signals most likely associated with the spike. Your result should compare each hour to the previous hour, flag the spike hours, and include the database, hour, latency, query volume, CPU, and active connections.
Schema
| Table | Column | Type | Description |
|---|
db_latency_metrics | metric_id | INT | Primary key |
db_latency_metrics | database_name | VARCHAR(100) | Database name |
db_latency_metrics | metric_hour | TIMESTAMP | Hourly metric timestamp |
db_latency_metrics | avg_latency_ms | NUMERIC(10,2) | Average latency in milliseconds |
db_latency_metrics | query_count | INT | Number of queries in the hour |
db_latency_metrics | active_connections | INT | Active connections during the hour |
db_latency_metrics | cpu_utilization_pct | NUMERIC(5,2) | CPU utilization percentage |
holiday_calendar | calendar_date | DATE | Holiday peak date |
holiday_calendar | holiday_name | VARCHAR(100) | Holiday label |
holiday_calendar | is_peak_hour | BOOLEAN | Whether the hour is in the peak window |
Sample data
db_latency_metrics | database_name | metric_hour | avg_latency_ms | query_count | active_connections | cpu_utilization_pct |
|---|
| 1 | by_orders | 2025-11-28 08:00:00 | 42.00 | 1200 | 85 | 61.00 |
| 2 | by_orders | 2025-11-28 09:00:00 | 44.00 | 1280 | 88 | 64.00 |
| 3 | by_orders | 2025-11-28 10:00:00 | 46.00 | 1310 | 90 | 66.00 |
| 4 | by_orders | 2025-11-28 11:00:00 | 118.00 | 2100 | 140 | 92.00 |
| 5 | by_orders | 2025-11-28 12:00:00 | 126.00 | 2250 | 148 | 95.00 |
| 6 | by_orders | 2025-11-29 10:00:00 | 48.00 | 900 | 70 | 55.00 |
| 7 | by_inventory | 2025-11-28 11:00:00 | 31.00 | 800 | 52 | 48.00 |
| 8 | by_inventory | 2025-11-28 12:00:00 | 33.00 | 820 | 54 | 49.00 |
| 9 | by_inventory | 2025-11-28 13:00:00 | 34.00 | 830 | 55 | 50.00 |
| 10 | by_inventory | 2025-11-28 14:00:00 | 79.00 | 1400 | 96 | 88.00 |
holiday_calendar | calendar_date | holiday_name | is_peak_hour |
|---|
| 2025-11-28 | Black Friday | true | |
| 2025-11-29 | Cyber Weekend | true | |
| 2025-11-30 | Cyber Weekend | false | |
Expected output
| database_name | metric_hour | avg_latency_ms | prev_hour_latency_ms | latency_increase_pct | query_count | active_connections | cpu_utilization_pct | holiday_name |
|---|
| by_orders | 2025-11-28 11:00:00 | 118.00 | 46.00 | 156.52 | 2100 | 140 | 92.00 | Black Friday |
| by_orders | 2025-11-28 12:00:00 | 126.00 | 118.00 | 6.78 | 2250 | 148 | 95.00 | Black Friday |
| by_inventory | 2025-11-28 14:00:00 | 79.00 | 34.00 | 132.35 | 1400 | 96 | 88.00 | Black Friday |
