CueObserve
Search…
Data Sources
Below are sample dataset queries for different data warehouses and databases.
BigQuery
1
SELECT
2
TIMESTAMP_TRUNC(CreatedTS, DAY) as OrderDate, -- HOUR or DAY granularity
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 400 DAY) -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
Redshift
1
SELECT
2
DATE_TRUNC('day', CreatedTS) as OrderDate, -- 'hour' or 'day' granularity
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(NVL(Order_Amount,0)) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATE_TRUNC('day', SYSDATE) - INTERVAL '400 days' -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
Snowflake
1
SELECT
2
DATE_TRUNC('DAY', CreatedTS) as OrderDate, -- 'HOUR' or 'DAY' granularity
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATE_TRUNC('DAY', CURRENT_TIMESTAMP) - INTERVAL '400 days' -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
Postgres
1
SELECT
2
DATE_TRUNC('day', CreatedTS) as OrderDate, -- 'hour' or 'day' granularity
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(COALESCE(Order_Amount,0)) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATE_TRUNC('day', now()) - INTERVAL '400 days' -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
MySQL
Hourly granularity
1
SELECT
2
DATE_FORMAT(CreatedTS, '%Y-%m-%d %H') as OrderDate,
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATE_SUB(DATE_FORMAT(now(), '%Y-%m-%d %H'), INTERVAL 21 DAY) -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
Daily granularity
1
SELECT
2
DATE(CreatedTS) as OrderDate,
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(IFNULL(Order_Amount,0)) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATE_SUB(CURDATE(), INTERVAL 400 DAY) -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
Druid
1
SELECT
2
DATE_TRUNC('DAY', __time) as OrderDate, -- 'HOUR' or 'DAY' granularity
3
City, State, -- dimensions
4
SUM("count") as Orders, SUM(Order_Amount) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '13' MONTH -- limit historical data to use for forecasting
7
GROUP BY 1, 2, 3
8
ORDER BY 1
Copied!
SQL Server
Hourly granularity
1
SELECT
2
CONVERT(datetime, format(CreatedTS,'yyyy-MM-dd HH:00:00')) as OrderDate,
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(Order_Amount) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATEADD(DAY, -21, cast(GETDATE() as date)) -- limit historical data to use for forecasting
7
GROUP BY format(CreatedTS,'yyyy-MM-dd HH:00:00'), City, State
8
ORDER BY 1
Copied!
Daily granularity
1
SELECT
2
CONVERT(datetime, format(CreatedTS,'yyyy-MM-dd 00:00:00')) as OrderDate,
3
City, State, -- dimensions
4
COUNT(1) as Orders, SUM(Order_Amount) as OrderAmount -- measures
5
FROM ORDERS
6
WHERE CreatedTS >= DATEADD(DAY, -400, cast(GETDATE() as date)) -- limit historical data to use for forecasting
7
GROUP BY format(CreatedTS,'yyyy-MM-dd 00:00:00'), City, State
8
ORDER BY 1
Copied!
Last modified 10mo ago