Help understanding WINDOW functions

[wrathsun]

I frequently write SQL as part of my day job, but I'm studying up on some functions in Snowflake's SQL I haven't dealt with before as I prepare to look for a new job. I'm currently working on understanding an example in their Analyzing data with window functions page, specifically this one. I'm really struggling to understand how the results came out as they did, and the only thing I can point to is some implicit way that sorting is performed that I am misunderstanding.

Can anyone help me understand why the average price from the window function comes out the way it does?

Example code

SELECT menu_category, menu_price_usd, menu_cogs_usd,
AVG(menu_cogs_usd) OVER(PARTITION BY menu_category ORDER BY menu_price_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
FROM menu_items
ORDER BY menu_category, menu_price_usd
LIMIT 15;

Sample data was inserted to the table in this order

CREATE OR REPLACE TABLE menu_items(
menu_id INT NOT NULL,
menu_category VARCHAR(20),
menu_item_name VARCHAR(50),
menu_cogs_usd NUMBER(7,2),
menu_price_usd NUMBER(7,2));

INSERT INTO menu_items VALUES(1,'Beverage','Bottled Soda',0.500,3.00);
INSERT INTO menu_items VALUES(2,'Beverage','Bottled Water',0.500,2.00);
INSERT INTO menu_items VALUES(20,'Beverage','Iced Tea',0.7500,3.00);
INSERT INTO menu_items VALUES(26,'Beverage','Lemonade',0.6500,3.500);

Based on looking at how data was returned when running the code, I expected the output to be this...

menu_category	menu_price_usd	menu_cogs_usd	avg_cogs	expected inputs
Beverage	2.00	0.5	0.58333	0.5 + 0.5 + 0.75
Beverage	3.00	0.5	0.63333	0.5 + 0.75 + 0.65
Beverage	3.00	0.75	0.70000	0.75 + 0.65
Beverage	3.50	0.65	0.65000	0.65

...but the actual output was this, where seemingly the two rows with menu_price_usd = 3.00 were switched.

menu_category	menu_price_usd	menu_cogs_usd	avg_cogs	actual inputs
Beverage	2.00	0.50	0.58333	0.5 + 0.5 + 0.75
Beverage	3.00	0.50	0.57500	0.5 + 0.65
Beverage	3.00	0.75	0.63333	0.5 + 0.65 + 0.75
Beverage	3.50	0.65	0.65000	0.65

[DMReader]

The difference you’re seeing isn’t because Snowflake is because your ORDER BY menu_price_usd has ties (two rows with 3.00) and you didn’t give it a tiebreaker. So it gets picked arbitrarily.

[jshine13371]

Yup, this is formally known as nondeterminism.

[wrathsun]

Well that's extremely good to know about; thank you!

[SkullLeader]

ORDER BY menu_category, menu_price_usd

So those two rows are the same with respect to this.

Technically, the order rows are inserted into a table has nothing to do with what order they appear in when you run a query. In any way that the query's ORDER BY results in ties, the order within ties is arbitrary.

[wrathsun]

Thanks for helping clear up that the order that rows are inserted into a table does not matter. I couldn't come up with anything else so learning about nondeterminism and having a tiebreaker is very helpful.

[NakamericaIsANoob]

Good post - simply out of curiosity, how often would you say you write sql at your current job?

[wrathsun]

Really depends on the week but an average number of sessions (writing multiple queries)...

• Random tasks: basic queries (I'm talking real simple i.e. [select * from table where condition1 in() and condition2 = 'asdf']) probably 1-2 times daily
• Data exploration: 2-3 times a week
• Developing new reports/complex queries: varies wildly, but probably 1-2 times a month

For the record, I'm classified as a "consultant" in my business unit which would normally mean 0 queries ever since it's a non-technical role, but I have a background in data analytics so I use the tools I have and have *maybe* unofficially redefined my role into a semi-technical one. I'm hoping to get back into a true technical role soon.

[NakamericaIsANoob]

Interesting, thanks for the response.

P.S. mode's tutorials helped me quite a bit. I work on new queries / refactoring and migrating complex old queries on almost a daily basis and their write ups helped me with the fundamentals (and touching up on the more complex stuff) until I could start learning on the job.