sources.md

Data Sources

Below are sample dataset queries for different data warehouses and databases.

BigQuery

SELECT
TIMESTAMP_TRUNC(CreatedTS, DAY) as OrderDate, -- HOUR or DAY granularity
City, State, -- dimensions
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 400 DAY)  -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

Redshift

SELECT
DATE_TRUNC('day', CreatedTS) as OrderDate, -- 'hour' or 'day' granularity
City, State, -- dimensions
COUNT(1) as Orders, SUM(NVL(Order_Amount,0)) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATE_TRUNC('day', SYSDATE) - INTERVAL '400 days'  -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

Snowflake

SELECT
DATE_TRUNC('DAY', CreatedTS) as OrderDate, -- 'HOUR' or 'DAY' granularity
City, State, -- dimensions
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATE_TRUNC('DAY', CURRENT_TIMESTAMP) - INTERVAL '400 days'  -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

Postgres

SELECT
DATE_TRUNC('day', CreatedTS) as OrderDate, -- 'hour' or 'day' granularity
City, State, -- dimensions
COUNT(1) as Orders, SUM(COALESCE(Order_Amount,0)) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATE_TRUNC('day', now()) - INTERVAL '400 days' -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

MySQL

Hourly granularity

SELECT
DATE_FORMAT(CreatedTS, '%Y-%m-%d %H') as OrderDate,
City, State, -- dimensions
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATE_SUB(DATE_FORMAT(now(), '%Y-%m-%d %H'), INTERVAL 21 DAY) -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

Daily granularity

SELECT
DATE(CreatedTS) as OrderDate,
City, State, -- dimensions
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATE_SUB(CURDATE(), INTERVAL 400 DAY) -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

Druid

SELECT
DATE_TRUNC('DAY', __time) as OrderDate, -- 'HOUR' or 'DAY' granularity
City, State, -- dimensions
SUM("count") as Orders, SUM(Order_Amount) as OrderAmount -- measures
FROM ORDERS
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '13' MONTH -- limit historical data to use for forecasting
GROUP BY 1, 2, 3
ORDER BY 1

SQL Server

Hourly granularity

SELECT
CONVERT(datetime, format(CreatedTS,'yyyy-MM-dd HH:00:00')) as OrderDate,
City, State, -- dimensions
COUNT(1) as Orders, SUM(Order_Amount) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATEADD(DAY, -21, cast(GETDATE() as date)) -- limit historical data to use for forecasting
GROUP BY format(CreatedTS,'yyyy-MM-dd HH:00:00'), City, State
ORDER BY 1

Daily granularity

SELECT
CONVERT(datetime, format(CreatedTS,'yyyy-MM-dd 00:00:00')) as OrderDate,
City, State, -- dimensions
COUNT(1) as Orders, SUM(Order_Amount) as OrderAmount -- measures
FROM ORDERS
WHERE CreatedTS >= DATEADD(DAY, -400, cast(GETDATE() as date)) -- limit historical data to use for forecasting
GROUP BY format(CreatedTS,'yyyy-MM-dd 00:00:00'), City, State
ORDER BY 1