SQL

CUBE+GROUP BY for Subtotals

Travis Tang

13 Dec 2022

We all have used GROUP BY, but do you know CUBE?

For example, if I have a table of user spending, I can get the subtotal of each user AND the total spend of all users combined, all within one table.

📌 Syntax is here.
𝚂𝙴𝙻𝙴𝙲𝚃
𝚞𝚜𝚎𝚛_𝚒𝚍, 𝚂𝚄𝙼(𝚜𝚙𝚎𝚗𝚍) 𝚊𝚖𝚝
𝙵𝚁𝙾𝙼 𝚞𝚜𝚎𝚛𝚜
𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 𝙲𝚄𝙱𝙴(𝚞𝚜𝚎𝚛_𝚒𝚍)

This saves you the need to create separate queries for generating the total.

As usual, full example below!

Another similar function is ROLLUP. Can anyone explain the difference between ROLLUP and 𝘾𝙐𝘽𝙀?

Use ARRAY_AGG to flatten columns to lists.

You need to convert a long table of values into a list? This is the most convenient function. 📌 Syntax: 𝚂𝙴𝙻𝙴𝙲𝚃 𝙰𝚁𝚁𝙰𝚈_𝙰𝙶𝙶(𝚌𝚘𝚕𝚞𝚖𝚗) 𝙵𝚁𝙾𝙼 𝚝𝚊𝚋𝚕𝚎 There you go, the column is flattened into a list separated by comma. ❌ To do so, I used to copy the entire column into a spreadsheet tool, transpose it, and use

Lazypredict: Run All Sklearn Algorithms With a Line Of Code

How to (and why you shouldn’t) use it

Generate Histogram Distribution

For a quick exploration of the distribution

Convert Jupyter Notebooks into Functions

Papermill is an open-sourced tool for parameterizing, executing, and analyzing Jupyter notebooks. Just pass in parameters to the notebook, and the Jupyter notebook runs automatically.

Read more

Use ARRAY_AGG to flatten columns to lists.

Lazypredict: Run All Sklearn Algorithms With a Line Of Code

Generate Histogram Distribution

Convert Jupyter Notebooks into Functions