Snowflake SQL Tutorial 57 - RATIO_TO_REPORT, WIDTH_BUCKET and CONDITIONAL_TRUE_EVENT

Snowflake SQL · Episode 57

In this post we are going to cover three Snowflake functions that almost nobody reaches for: RATIO_TO_REPORT, WIDTH_BUCKET and CONDITIONAL_TRUE_EVENT.

RATIO_TO_REPORT gives you a row’s share of the total. WIDTH_BUCKET takes a number and tells you which band it falls into, with the bands defined by you. CONDITIONAL_TRUE_EVENT takes a condition instead of a column and counts how many times that condition has been true so far.

We will take them one at a time, starting with the simplest form of each. Along the way we will see what a NULL does to a share, which band disappears from a grouped result and why, and where a value outside your range ends up. At the end we will turn that third counter into a run id, which gives us sessionisation in one function call.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 57 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with RATIO_TO_REPORT
3. Now let's take a share of the department instead
4. What does a NULL do to a share?
5. Now let's put the salaries into bands with WIDTH_BUCKET
6. Is that the same thing as NTILE?
7. What happens to a value outside the range?
8. Now let's count events with CONDITIONAL_TRUE_EVENT
9. Can we use that same number as a run id?

Let's create some sample data

The first half of this page works on EMPLOYEES and the second half on ORDERS. They are the same two tables the whole series uses, so if you have already run the shared setup script you can skip straight past these.

Sample data — EMPLOYEES

-- EMPLOYEES comes from Episode 1. Every episode in this series reuses the
-- same tables, so you only set them up once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Sample data — ORDERS

-- ORDERS is the table the last two sections of this page use. It is part of
-- the same shared sample-data script; run it once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS (
    ORDER_ID     INT,
    CUSTOMER_ID  INT,
    ORDER_DATE   DATE,
    SHIP_DATE    DATE,
    STATUS       VARCHAR,
    AMOUNT       NUMBER(10,2),
    QUANTITY     INT
);

INSERT INTO ORDERS VALUES
    (1001, 101, '2023-01-12', '2023-01-15', 'SHIPPED',   4200.00,  12),
    (1002, 102, '2023-01-28', '2023-02-02', 'SHIPPED',    890.50,   3),
    (1003, 101, '2023-02-14', '2023-02-16', 'SHIPPED',   6150.00,  20),
    (1004, 103, '2023-02-27', NULL,         'CANCELLED', 1500.00,   5),
    (1005, 104, '2023-03-09', '2023-03-14', 'SHIPPED',   2310.75,   8),
    (1006, 101, '2023-03-22', '2023-03-24', 'SHIPPED',   5020.00,  16),
    (1007, 105, '2023-04-05', '2023-04-11', 'SHIPPED',   7890.00,  25),
    (1008, 102, '2023-04-19', '2023-04-23', 'SHIPPED',   1120.00,   4),
    (1009, 103, '2023-05-02', '2023-05-06', 'SHIPPED',   3340.25,  11),
    (1010, 106, '2023-05-21', NULL,         'PENDING',    640.00,   2),
    (1011, 101, '2023-06-08', '2023-06-10', 'SHIPPED',   8100.00,  27),
    (1012, 104, '2023-06-25', '2023-06-30', 'SHIPPED',   1975.50,   7),
    (1013, 105, '2023-07-11', '2023-07-15', 'SHIPPED',   6420.00,  21),
    (1014, 102, '2023-07-29', '2023-08-03', 'RETURNED',   750.00,   3),
    (1015, 107, '2023-08-14', '2023-08-17', 'SHIPPED',   9250.00,  30),
    (1016, 103, '2023-08-30', '2023-09-04', 'SHIPPED',   2860.00,   9),
    (1017, 101, '2023-09-16', '2023-09-19', 'SHIPPED',   5540.00,  18),
    (1018, 106, '2023-10-03', '2023-10-08', 'SHIPPED',   1430.00,   5),
    (1019, 105, '2023-10-22', '2023-10-26', 'SHIPPED',   7015.00,  23),
    (1020, 107, '2023-11-07', '2023-11-10', 'SHIPPED',  10400.00,  34),
    (1021, 102, '2023-11-24', '2023-11-29', 'SHIPPED',    980.00,   4),
    (1022, 104, '2023-12-09', NULL,         'CANCELLED', 2250.00,   8),
    (1023, 101, '2023-12-27', '2023-12-30', 'SHIPPED',   6730.00,  22),
    (1024, 103, '2024-01-15', '2024-01-18', 'SHIPPED',   3910.00,  13),
    (1025, 107, '2024-01-31', '2024-02-05', 'SHIPPED',  11200.00,  36),
    (1026, 105, '2024-02-17', '2024-02-21', 'SHIPPED',   8340.00,  27),
    (1027, 106, '2024-03-04', '2024-03-09', 'SHIPPED',   1690.00,   6),
    (1028, 102, '2024-03-21', '2024-03-25', 'SHIPPED',   1240.00,   5),
    (1029, 101, '2024-04-06', '2024-04-09', 'SHIPPED',   7460.00,  24),
    (1030, 109, '2024-04-23', '2024-04-28', 'SHIPPED',   5180.00,  17),
    (1031, 103, '2024-05-10', '2024-05-14', 'SHIPPED',   4025.00,  14),
    (1032, 107, '2024-05-28', '2024-06-01', 'SHIPPED',  12850.00,  41),
    (1033, 105, '2024-06-13', NULL,         'PENDING',   6900.00,  22),
    (1034, 109, '2024-06-30', '2024-07-04', 'SHIPPED',   4760.00,  16),
    (1035, 101, '2024-07-18', '2024-07-21', 'SHIPPED',   8820.00,  29),
    (1036, 104, '2024-08-02', '2024-08-07', 'SHIPPED',   2540.00,   9),
    (1037, 107, '2024-08-25', '2024-08-29', 'SHIPPED',  13100.00,  43),
    (1038, 109, '2024-09-11', '2024-09-15', 'SHIPPED',   5930.00,  19),
    (1039, 102, '2024-09-29', '2024-10-03', 'RETURNED',  1080.00,   4),
    (1040, 101, '2024-10-16', '2024-10-19', 'SHIPPED',   9640.00,  31);

-- One deliberate NULL amount, inserted separately so it is easy to see why it
-- is there.
INSERT INTO ORDERS VALUES
    (1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);

Two of the three functions on this page are window functions. They take an OVER clause, they return one value per row, and they do not collapse the result the way GROUP BY does. If that sentence needs unpacking, Episode 46 is where the OVER clause starts. The third one is a plain scalar function, and we will say so when we get to it.

Let's start with RATIO_TO_REPORT

RATIO_TO_REPORT answers one question. How much of the total is this row? We give it an expression, we give it an OVER clause, and it divides the row’s value by the sum of that value across the window.

Query

SELECT FIRST_NAME, SALARY,
       RATIO_TO_REPORT(SALARY) OVER () AS SHARE_OF_PAYROLL
FROM EMPLOYEES
ORDER BY SHARE_OF_PAYROLL DESC;

Result8 rows · 3 columns

FIRST_NAME	SALARY	SHARE_OF_PAYROLL
Daniel	131000.00	0.16443859
Aisha	118000.00	0.14812025
Lena	104000.00	0.13054666
Marcus	96500.00	0.12113224
Priya	96500.00	0.12113224
Grace	92000.00	0.11548359
Tomas	87250.00	0.10952112
Sofia	71400.00	0.08962531

OVER () with nothing inside it means the whole result set is the window. So the denominator here is the entire payroll. Daniel’s 0.16443859 is his salary as a fraction of it, and Sofia’s 0.08962531 is hers.

Fraction, not percent

The function returns a fraction, not a percentage. If a report wants a percentage, multiply the result by a hundred and wrap it in ROUND yourself. RATIO_TO_REPORT will do neither of those for you.

We already know how to do this by hand

Nothing we have run so far needs a new function. Episode 46 showed the same calculation written out longhand. Divide the row’s value by a windowed SUM. So let's run both in one query and compare them column by column, rather than take the equivalence on trust.

Query — the hand-written version beside the built-in one

SELECT FIRST_NAME, SALARY,
       SUM(SALARY) OVER ()                 AS PAYROLL,
       SALARY / SUM(SALARY) OVER ()        AS BY_HAND,
       RATIO_TO_REPORT(SALARY) OVER ()     AS BUILT_IN
FROM EMPLOYEES
ORDER BY SALARY DESC;

Result8 rows · 5 columns

FIRST_NAME	SALARY	PAYROLL	BY_HAND	BUILT_IN
Daniel	131000.00	796650.00	0.16443859	0.16443859
Aisha	118000.00	796650.00	0.14812025	0.14812025
Lena	104000.00	796650.00	0.13054666	0.13054666
Marcus	96500.00	796650.00	0.12113224	0.12113224
Priya	96500.00	796650.00	0.12113224	0.12113224
Grace	92000.00	796650.00	0.11548359	0.11548359
Tomas	87250.00	796650.00	0.10952112	0.10952112
Sofia	71400.00	796650.00	0.08962531	0.08962531

BY_HAND and BUILT_IN are identical on all eight rows. So RATIO_TO_REPORT is a shorthand, and that is the honest way to describe it. It saves us writing the window twice, and writing it twice is where a typo gets in.

The PAYROLL column is the denominator both of them are using, made visible.

And do the shares add up to one?

That is the kind of claim worth checking rather than assuming, so let's check it.

Query — the arithmetic check

SELECT SUM(SHARE) AS TOTAL_SHARE, COUNT(*) AS ROWS_COUNTED
FROM (SELECT RATIO_TO_REPORT(SALARY) OVER () AS SHARE FROM EMPLOYEES);

Result1 row · 2 columns

TOTAL_SHARE	ROWS_COUNTED
1.00000000	8

1.00000000, across the 8 rows that were counted. Every row’s share, added together, is the whole thing.

Now let's take a share of the department instead

RATIO_TO_REPORT takes a PARTITION BY like every other window function in this level. The partition is what decides which total the share is a share of. Let's partition by DEPT_ID and run it again.

Query

SELECT DEPT_ID, FIRST_NAME, SALARY,
       RATIO_TO_REPORT(SALARY) OVER (PARTITION BY DEPT_ID) AS SHARE_OF_DEPT
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SHARE_OF_DEPT DESC;

Result8 rows · 4 columns

DEPT_ID	FIRST_NAME	SALARY	SHARE_OF_DEPT
10	Daniel	131000.00	0.37916064
10	Aisha	118000.00	0.34153401
10	Marcus	96500.00	0.27930535
20	Lena	104000.00	0.54379085
20	Tomas	87250.00	0.45620915
30	Priya	96500.00	0.57474687
30	Sofia	71400.00	0.42525313
NULL	Grace	92000.00	1.00000000

Same function, same column, completely different numbers. Daniel’s share went up, because he is no longer measured against the whole company. He is measured against department 10.

Grace comes back with 1.00000000. She is the only employee whose DEPT_ID is NULL, so her partition holds exactly one row, and one row is all of its own total.

Let's check the arithmetic again, this time per partition.

Query — every partition checked separately

SELECT DEPT_ID, SUM(SHARE) AS TOTAL_SHARE, COUNT(*) AS PEOPLE
FROM (SELECT DEPT_ID, RATIO_TO_REPORT(SALARY) OVER (PARTITION BY DEPT_ID) AS SHARE
      FROM EMPLOYEES)
GROUP BY DEPT_ID ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 3 columns

DEPT_ID	TOTAL_SHARE	PEOPLE
10	1.00000000	3
20	1.00000000	2
30	1.00000000	2
NULL	1.00000000	1

Four partitions, four totals of 1.00000000. The PEOPLE column gives us the sizes: three in department 10, two in 20, two in 30, and Grace on her own.

NULL is a partition, not an absence

Look at what happened to NULL here. PARTITION BY DEPT_ID treats all the NULL departments as one partition, not as a partition each. There is only one such row in this table, so we cannot tell the difference from this grid alone. On your data you can, and it matters.

What does a NULL do to a share?

SALARY has no NULLs, which makes it a poor test. COMMISSION_PCT is mostly NULL, so let's run the same function against that instead.

Query

SELECT FIRST_NAME, COMMISSION_PCT,
       RATIO_TO_REPORT(COMMISSION_PCT) OVER () AS SHARE
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 3 columns

FIRST_NAME	COMMISSION_PCT	SHARE
Aisha	NULL	NULL
Marcus	NULL	NULL
Lena	0.05	0.21739130
Tomas	NULL	NULL
Priya	0.10	0.43478261
Daniel	NULL	NULL
Sofia	0.08	0.34782609
Grace	NULL	NULL

Five of the eight rows have no commission, and every one of them comes back with a NULL share. A NULL in gives us a NULL out. Not a zero, and not an error.

Now look at the three rows that do have a value. Lena, Priya and Sofia come back with 0.21739130, 0.43478261 and 0.34782609, and those three add up to exactly one between them.

The denominator is the sum of what is actually there. The five missing values are not counted as zero in the total. That is why the three real shares still make a whole rather than a fraction of one.

Read the denominator before you quote the share

That is convenient and it is also a trap, because the shares no longer describe the population you think they do. 0.43478261 is Priya’s share of the commissions that exist. It is not her share of the commission the company pays its eight employees. If some of those NULLs ought to be zeros, use COALESCE before the window function, not after it.

Now let's put the salaries into bands with WIDTH_BUCKET

Second function, and a change of subject. WIDTH_BUCKET takes a number and tells us which band it falls into. We define the bands ourselves: a low end, a high end, and how many bands to cut the range into.

Query

SELECT FIRST_NAME, SALARY,
       WIDTH_BUCKET(SALARY, 70000, 140000, 7) AS BAND
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 3 columns

FIRST_NAME	SALARY	BAND
Sofia	71400.00	1
Tomas	87250.00	2
Grace	92000.00	3
Marcus	96500.00	3
Priya	96500.00	3
Lena	104000.00	4
Aisha	118000.00	5
Daniel	131000.00	7

WIDTH_BUCKET(SALARY, 70000, 140000, 7) asks for the range from 70000 to 140000 cut into seven equal bands, so each band covers a seventh of that range. Sofia lands in band 1, Daniel in band 7, and Grace, Marcus and Priya all share band 3.

The odd one out

This one is not a window function and it needs no OVER clause. It is a scalar function that happens to be taught here, because it is so often compared with NTILE. You can use it in a WHERE clause, in a GROUP BY, anywhere.

Now let's group by the band and count the rows

The obvious next step is to turn that into a histogram. Group by the band and count what is in each one.

Query

SELECT BAND, COUNT(*) AS PEOPLE, MIN(SALARY) AS LOWEST, MAX(SALARY) AS HIGHEST
FROM (SELECT SALARY, WIDTH_BUCKET(SALARY, 70000, 140000, 7) AS BAND FROM EMPLOYEES)
GROUP BY BAND ORDER BY BAND;

Result6 rows · 4 columns

BAND	PEOPLE	LOWEST	HIGHEST
1	1	71400.00	71400.00
2	1	87250.00	87250.00
3	3	92000.00	96500.00
4	1	104000.00	104000.00
5	1	118000.00	118000.00
7	1	131000.00	131000.00

Six rows came back, from a seven-band query. Read the BAND column down: 1, 2, 3, 4, 5, 7. Band 6 is not there at all.

It is not missing because of an error. It is missing because the band itself is empty. Band 6 covers the stretch between Aisha’s 118000.00 and Daniel’s 131000.00, and nobody in this table earns in it. A band with no rows produces no group, and a group that does not exist cannot turn up in a GROUP BY result.

The band that vanishes

This is the detail that bites when you are building a histogram or a chart. An empty band simply does not come back. So your bars shift left and the gap disappears instead of showing as zero. If you need every band represented, generate the band numbers separately and LEFT JOIN your counts onto them. WIDTH_BUCKET will not do it for you.

Is that the same thing as NTILE?

NTILE from Episode 51 also hands out bucket numbers, which makes the two look interchangeable. They are not. Here is the sentence to keep: NTILE makes buckets of equal count, WIDTH_BUCKET makes buckets of equal width.

Let's put four bands next to four tiles and read both columns.

Query — four bands against four tiles

SELECT FIRST_NAME, SALARY,
       WIDTH_BUCKET(SALARY, 70000, 140000, 4) AS EQUAL_WIDTH,
       NTILE(4) OVER (ORDER BY SALARY)        AS EQUAL_COUNT
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 4 columns

FIRST_NAME	SALARY	EQUAL_WIDTH	EQUAL_COUNT
Sofia	71400.00	1	1
Tomas	87250.00	1	1
Grace	92000.00	2	2
Marcus	96500.00	2	2
Priya	96500.00	2	3
Lena	104000.00	2	3
Aisha	118000.00	3	4
Daniel	131000.00	4	4

Sofia, Tomas, Grace and Marcus get the same number from both columns. Priya, Lena and Aisha do not. Each of those three carries one number under EQUAL_WIDTH and a different one under EQUAL_COUNT.

Count down each column and the reason is plain. EQUAL_COUNT puts exactly two of the eight rows in every tile, because that is what NTILE is for. EQUAL_WIDTH puts four of the eight in band 2 and leaves bands 3 and 4 holding one row each. The salaries are bunched in the middle of the range, and WIDTH_BUCKET does not care how many people land in a band.

	WIDTH_BUCKET	NTILE
What is held constant	The width of each band	The number of rows in each bucket
Who defines the edges	You do, as arguments	The data does, by where the rows fall
Needs OVER	No, it is a scalar function	Yes, it is a window function
Needs an ordering	No	Yes, inside OVER
Empty bucket possible	Yes, and it disappears from a grouped result	No, every bucket gets rows
Comparable across queries	Yes, the same arguments always mean the same bands	No, the edges move when the data does

Comparing one query with another is what decides most real cases. If we want this month’s distribution next to last month’s, we need fixed edges, which means WIDTH_BUCKET. If we want the top quartile of whatever is in front of us, we need NTILE.

What happens to a value outside the range?

So far every salary has been inside the range we named. Let's narrow the bands deliberately and find out what happens to the ones that fall outside. We ask for 90000 to 110000 in four bands, on the same eight salaries.

Query

SELECT FIRST_NAME, SALARY,
       WIDTH_BUCKET(SALARY, 90000, 110000, 4) AS BAND
FROM EMPLOYEES
ORDER BY SALARY;

Result8 rows · 3 columns

FIRST_NAME	SALARY	BAND
Sofia	71400.00	0
Tomas	87250.00	0
Grace	92000.00	1
Marcus	96500.00	2
Priya	96500.00	2
Lena	104000.00	3
Aisha	118000.00	5
Daniel	131000.00	5

Sofia and Tomas earn less than the bottom of the range and come back as band 0. Aisha and Daniel earn more than the top and come back as band 5, which is n + 1 for a four-band query.

No error, and no NULL. Out-of-range values are given two extra buckets of their own. One underflow bucket numbered 0, and one overflow bucket numbered n + 1. Which is useful, and silent.

Let's group it so the extra buckets are countable.

Query — grouped, so the extra buckets are countable

SELECT BAND, COUNT(*) AS PEOPLE, MIN(SALARY) AS LOWEST, MAX(SALARY) AS HIGHEST
FROM (SELECT SALARY, WIDTH_BUCKET(SALARY, 90000, 110000, 4) AS BAND FROM EMPLOYEES)
GROUP BY BAND ORDER BY BAND;

Result5 rows · 4 columns

BAND	PEOPLE	LOWEST	HIGHEST
0	2	71400.00	87250.00
1	1	92000.00	92000.00
2	2	96500.00	96500.00
3	1	104000.00	104000.00
5	2	118000.00	131000.00

Two buckets you did not ask for

Four of the eight employees are now in a bucket we never asked for, and nothing in the query says so. If you group by WIDTH_BUCKET and plot the result, bucket 0 and bucket n + 1 will quietly sit at either end of your chart holding everything that did not fit. Check for them every time.

And the vanishing band is here too. The BAND column reads 0, 1, 2, 3, 5. Band 4 is missing, because the stretch between Lena’s 104000.00 and the top of the range holds nobody. Same behaviour as before, one range up.

Now let's count events with CONDITIONAL_TRUE_EVENT

Third function, and the least obvious name in Snowflake. CONDITIONAL_TRUE_EVENT takes a condition rather than a column, and returns the number of times that condition has been true so far.

Put plainly, it is a running count of events. Let's count cancellations through ORDERS in date order.

Query

SELECT ORDER_ID, ORDER_DATE, STATUS,
       CONDITIONAL_TRUE_EVENT(STATUS = 'CANCELLED') OVER (ORDER BY ORDER_DATE, ORDER_ID)
         AS CANCELLATIONS_SO_FAR
FROM ORDERS
ORDER BY ORDER_DATE, ORDER_ID
LIMIT 14;

Result14 rows · 4 columns

ORDER_ID	ORDER_DATE	STATUS	CANCELLATIONS_SO_FAR
1001	2023-01-12	SHIPPED	0
1002	2023-01-28	SHIPPED	0
1003	2023-02-14	SHIPPED	0
1004	2023-02-27	CANCELLED	1
1005	2023-03-09	SHIPPED	1
1006	2023-03-22	SHIPPED	1
1007	2023-04-05	SHIPPED	1
1008	2023-04-19	SHIPPED	1
1009	2023-05-02	SHIPPED	1
1010	2023-05-21	PENDING	1
1011	2023-06-08	SHIPPED	1
1012	2023-06-25	SHIPPED	1
1013	2023-07-11	SHIPPED	1
1014	2023-07-29	RETURNED	1

Read CANCELLATIONS_SO_FAR down the column. The first three orders are SHIPPED, so the count is 0. Nothing has happened yet. Order 1004 is CANCELLED, and on that row the count becomes 1. Then it stays at 1 for every row after it, including the PENDING and RETURNED ones, because neither of those makes the condition true.

It needs an ORDER BY

ORDER BY inside OVER is not optional here. So far means nothing without an order, and Snowflake will reject the function without one. The order you give it is the order the counting happens in, so choose it deliberately.

That grid is the first 14 orders, because its own query says LIMIT 14, and exactly one cancellation falls inside those rows. So let's look at the statuses across the whole table, to put the one we can see in context.

Query — what the whole table holds

SELECT STATUS, COUNT(*) AS N FROM ORDERS GROUP BY STATUS ORDER BY N DESC;

Result4 rows · 2 columns

STATUS	N
SHIPPED	34
PENDING	3
RETURNED	2
CANCELLED	2

SHIPPED 34, PENDING 3, CANCELLED 2, RETURNED 2. That adds to 41 orders, with two cancellations in the lot. The counter found the first of them. The second one arrives later in the year, past the end of a grid limited to 14 rows.

Can we use that same number as a run id?

Counting cancellations is the obvious use and it is not the interesting one. Let's change two things. We make the condition anything other than shipped, and we partition by customer. Now the number stops meaning how many and starts meaning which run you are in.

Query

SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS,
       CONDITIONAL_TRUE_EVENT(STATUS <> 'SHIPPED')
         OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE, ORDER_ID) AS RUN_ID
FROM ORDERS
ORDER BY CUSTOMER_ID, ORDER_DATE, ORDER_ID
LIMIT 14;

Result14 rows · 5 columns

CUSTOMER_ID	ORDER_ID	ORDER_DATE	STATUS	RUN_ID
101	1001	2023-01-12	SHIPPED	0
101	1003	2023-02-14	SHIPPED	0
101	1006	2023-03-22	SHIPPED	0
101	1011	2023-06-08	SHIPPED	0
101	1017	2023-09-16	SHIPPED	0
101	1023	2023-12-27	SHIPPED	0
101	1029	2024-04-06	SHIPPED	0
101	1035	2024-07-18	SHIPPED	0
101	1040	2024-10-16	SHIPPED	0
102	1002	2023-01-28	SHIPPED	0
102	1008	2023-04-19	SHIPPED	0
102	1014	2023-07-29	RETURNED	1
102	1021	2023-11-24	SHIPPED	1
102	1028	2024-03-21	SHIPPED	1

Customer 101 fills the first nine rows. Every order SHIPPED, every RUN_ID 0. The condition never fired, so there is one unbroken run.

Then customer 102 takes the remaining five rows. Two shipped orders at RUN_ID 0, then order 1014 comes back RETURNED, and from that row on the RUN_ID is 1. That is true of the returned order itself and of everything after it.

Every time the condition fires, the number steps up and never comes back down. Which means it is a group id for the stretch between events. And a group id is something we can GROUP BY.

Query — group by the run id and the runs fall out

WITH R AS (
  SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, AMOUNT,
         CONDITIONAL_TRUE_EVENT(STATUS <> 'SHIPPED')
           OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE, ORDER_ID) AS RUN_ID
  FROM ORDERS
)
SELECT CUSTOMER_ID, RUN_ID, COUNT(*) AS ORDERS_IN_RUN, SUM(AMOUNT) AS RUN_TOTAL
FROM R
WHERE STATUS = 'SHIPPED'
GROUP BY CUSTOMER_ID, RUN_ID
ORDER BY ORDERS_IN_RUN DESC, CUSTOMER_ID, RUN_ID
LIMIT 12;

Result10 rows · 4 columns

CUSTOMER_ID	RUN_ID	ORDERS_IN_RUN	RUN_TOTAL
101	0	9	61660.00
107	0	5	56800.00
103	1	4	14135.25
105	0	4	29665.00
109	0	3	15870.00
102	0	2	2010.50
102	1	2	2220.00
104	0	2	4286.25
106	1	2	3120.00
104	1	1	2540.00

Customer 101’s unbroken run of nine shipped orders is worth 61660.00. Customer 102 turns up twice, as RUN_ID 0 and RUN_ID 1, with two orders in each. Order 1014 coming back RETURNED started a new run id for them, and the shipped orders either side of it are now in two different groups.

The ORDERS_IN_RUN column adds up to 34, which is the SHIPPED count in the status grid. Nothing was lost and nothing was double-counted. The query also carries a LIMIT 12 that never bites. Only ten runs contain a shipped order at all, so this grid is the whole result.

What we are actually looking at

This is sessionisation, also known as the gaps-and-islands problem, and it is one function call. The usual way to do it is a self-join, or LAG plus a conditional flag plus a running SUM over that flag. CONDITIONAL_TRUE_EVENT is that running SUM, already written.

Key points

• RATIO_TO_REPORT(x) OVER (...) is shorthand for x / SUM(x) OVER (...). The page runs both and they match cell for cell. The saving is writing the window once instead of twice.
• It returns a fraction, not a percentage, and it takes PARTITION BY like every other window function. The partition decides which total the share is a share of.
• A NULL input gives a NULL share, and the denominator is the sum of the values that are actually present. So the surviving shares still add to one, over a smaller population than you may think.
• WIDTH_BUCKET makes bands of equal width; NTILE makes buckets of equal count. Fixed edges you can compare across queries, or edges that move with the data. Pick on that basis.
• WIDTH_BUCKET is a scalar function, not a window function. No OVER clause, and it works anywhere an expression works.
• An empty band produces no group, so it is absent from a grouped result rather than present with a count of zero. Fill the gaps yourself if you are drawing a histogram.
• Values below your range come back as band 0 and values above it as band n + 1. No error, no NULL, and nothing in the query to remind you to look.
• CONDITIONAL_TRUE_EVENT takes a condition, not a column, and counts how many times it has been true so far. It requires an ORDER BY inside OVER.
• That same counter is a run id. It steps up on every event and holds steady between them, so you can GROUP BY it to get sessions, streaks and islands in one function call.

Next in this series

In the next post we will cover Top-N per group. It is the pattern you will write more often than any other in this level. We will look at the three functions that can go in its ranking slot, and at a NULL that puts the wrong row at number one. That post closes Level 5.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EMPLOYEES and ORDERS data is invented demo data created by that script, and the amounts carry no currency. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the RATIO_TO_REPORT documentation, the WIDTH_BUCKET documentation and the CONDITIONAL_TRUE_EVENT documentation.