How To Create A Real Time Calendar Table Using SQL

From dreamstime

In visualization world, choosing a good chart is critical to gain more insight without extra explanation. We have seen too many bar charts, tables, and pie charts in many dashboards, here is one more interesting and useful chart we can use to track the real time status change: calendar table.

Example: You may have seen Google calendar showing the daily scheduled events at an hourly basis

I will use the below example to explain step by step. The question to answer is: How many orders at what time are placed weekly?

At first, prepare the target table which will decide what values to display on calendar.

CREATE TABLE fact_orders 
(order_id TEXT, placed_at TIMESTAMP, hour_of_day INTEGER, day_name TEXT);
INSERT INTO fact_orders VALUES
(1, '2019-09-16 10:25:12', 10, 'Monday'),
(2, '2019-09-13 09:36:35', 9, 'Friday'),
(3, '2019-09-16 08:45:42', 8, 'Monday'),
(4, '2019-09-13 03:36:35', 3, 'Friday'),
(5, '2019-09-14 06:36:35', 6, 'Saturday'),
(6, '2019-09-17 10:25:12', 10, 'Tuesday');

There is a template table to have 24 hours weekly mapping.

CREATE TABLE dim_hour (day_name TEXT, hour_of_day INTEGER);
INSERT INTO dim_hour VALUES…