SQL Macros

SQL macros take away some of the tedious and verbose parts of writing SQL.

Timestamp macros

Instead of the standard SQL date_trunc function using date_trunc(${interval}, timestamp), you can use [timestamp:${interval}] where valid ${interval} is one of:

• microseconds
• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

Example

Let's assume you have a table with a joined_date that you want to aggregate monthly. Without the macro, you'd be writing for PostgreSQL / Redshift:

select date_trunc('month', joined_date::timestamp), count(id)
from customers
group by 1;

and for SQL Server:

select dateadd(month, datediff(month, 0, joined_date), 0), count(id)
from customers
group by 1;

With the macro, you can simply write:

select [joined_date:month], count(id)
from customers
group by 1;

Special Dashboard macro: aggregation

The special macro aggregation makes the aggregation interval user configurable: one amongst year, quarter, month, week and day. The default is quarter if no aggregation is picked. An example:

select [joined_date:aggregation], count(id)
from customers
group by 1;

Dashboard macro: daterange

The special macro daterange makes the time axis range user configurable: between relative periods this_week, this_month, last_30days, and absolute periods.

Example

Let's assume you have a table with a column joined_date that you want to apply the date range filter. Your query will look like the following (with an additional aggregation thrown in):

select [joined_date:aggregation], count(id)
from customers
where [joined_date=daterange]
group by 1;

The above query will get rewritten as for PostgreSQL / Redshift:

select date_trunc('quarter', joined_date), count(id)
from customers
where joined_date between 'start_user_picked_date'::timestamp and 'end_user_picked_date'::timestamp
group by 1;

and for SQL Server:

select dateadd(quarter, datediff(quarter, 0, joined_date), 0), count(id)
from customers
where joined_date >= cast('start_user_picked_date' as date) and joined_date < cast('end_user_picked_date' as date)
group by 1;

To filter just on the start [daterange_start] or the end [daterange_end] of the date range, use the following:

select [joined_date:aggregation], count(id)
from customers
where joined_date < [daterange_end]

Possible Values

If you rather hardcode the date range filter, instead of making it user configurable, the following are supported:

• last_week
• this_week
• this_month
• last_30days
• last_quarter
• this_quarter
• last_year
• this_year

These date ranges are relative, and will always reflect the time they were run at.

For example with the SQL Server dialect,

declare @dt as datetime = '2017-05-13T01:17:42Z';
select 'Hello'
where [@dt=last_week]

gets rewritten as:

declare @dt as datetime = '2017-05-13T01:17:42Z';
select 'Hello'
where @dt >= cast('2017-10-09' as date) and @dt <= cast('2017-10-16' as date)

Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.