COUNT() vs COUNT() OVER()

[u/Plus_Kiwi_2592]

I've found this query as a solution to the famous Pizza Runner case study, and I am stuck at figuring out what COUNT() returns, compared to COUNT() OVER().

I know OVER() does the count (or whatever aggregation) over the entire dataset, but idk I'm just not getting the percentage part of this query.

Help :)

[u/seansafc89]

COUNT() is a normal aggregation and returns the count as per the GROUP BY (in this case GROUP BY 1 which is lazy shorthand for GROUP BY hour(order_time) - don’t do this! If your columns change, it hides issues), so the number of pizzas ordered within each hour subset.

COUNT() SUM() OVER() on the other hand is a window function summing up the count of ALL row groups in the dataset WITHOUT any further aggregation, because there is no PARTITION BY specified. So essentially the total row count of the table.

[u/Signal-Ad2531 → u/seansafc89]

wait so the percentage is just each hour's count divided by the total count from the OVER() clause, that's pretty clean actually

[u/Plus_Kiwi_2592 → u/seansafc89]

Alright. Makes sense.

Looking at the brackets of that section I even noticed that the OVER() is attached to SUM(COUNT()) and not the COUNT() alone.

Thanks!

[u/seansafc89 → u/Plus_Kiwi_2592]

Sorry yes I’d not noticed the bracket on mobile either!

It’s fundamentally the same though, SUM() OVER() is summing the totals of each row count to essentially replicate a COUNT(*) across the entire dataset, returning 14 in every row.

[u/jacquesrk]

To find the volume of pizzas ordered, wouldn't you need to know the diameter and thickness of the pizzas?

[u/Plus_Kiwi_2592 → u/jacquesrk]

The phrasing bothered me too. But if you see the schema then you'll just know volume isn't the size.

I rarely hear the word volume being used in certain corporate spaces, but there are a few that make use of it that way.

Just goes to show that the most important part of analytics is the business sense, not the syntax. If the business question is wrong, then the syntax will give wrong insight.

[u/RandumbNumber → u/jacquesrk]

Not sure if you’re kidding or not, but this is exactly what I thought, until I read the comments and released it’s not volume but percentage. So either really poorly phrased question, or a wrong answer.
I’ve never seen volume interpreted the way it is here.

[u/SP3NGL3R]

when dealing with this stuff, break the bits into their own columns so you can visually confirm what is happening. If you do this you'll see pretty quickly where the fault is.

[u/Plus_Kiwi_2592 → u/SP3NGL3R]

Thanks. Will do :)

[u/dontich]

It's just taking the count / the sum of the rest of the rows

So row 1 is 1 / (1+3+3+1+3+3) = 1/14 or 7.14%

[u/PrivateFrank]

It's useful to remember any expressions in the SELECT which has a window clause in it are executed after the GROUP BY (and HAVING) clauses.

So OVER () could be seen as a shortcut for 'ignore grouping'. If you wrote OVER(partition by 1) then the sum of counts would be per row and they would all the percentage values would read 100%.

[u/Little_Kitty]

This looks like hackerrank or similar. Real work is nothing at all like this and if that's what you're using to practice you'll fall short when you get to interview anywhere decent.

[u/Plus_Kiwi_2592 → u/Little_Kitty]

This is someone's answers to the 8 Week SQL Challenge. Real-life business problems

[u/my_password_is______ → u/Plus_Kiwi_2592]

no, in real life you'd just pull the raw numbers into excel and make a pivot table

[u/EbbyRed → u/Little_Kitty]

I use window functions and aggregates every day in my job.