SQL

SQL

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

SQL

Generate Histogram Distribution

For a quick exploration of the distribution

SQL

Pivot Table (From Long to Wide)

Every data scientist, scientist, and engineer should know how to create a pivot table. 𝙲𝙰𝚂𝙴 𝚆𝙷𝙴𝙽 is the best way to do so in SQL. 🚀 Here are THREE ways to pivot a table for monthly sales. 1️⃣ We find the 𝚂𝚄𝙼 of all salesperson each month. Here, use 𝚂𝚄𝙼(𝙲𝙰𝚂𝙴 𝚆𝙷𝙴𝙽). 2️⃣ We find the average

SQL

Find duplicates with RANK

Let's clean out duplicates! Here's how. 1️⃣ Check if duplicates exists. Use 𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 + 𝙲𝙾𝚄𝙽𝚃(*) to find duplicates. See example 1 in image below. 2️⃣ Identify exact rows that are duplicated. Use 𝚁𝙰𝙽𝙺 𝙾𝚅𝙴𝚁(𝙿𝙰𝚁𝚃𝙸𝚃𝙸𝙾𝙽 𝙱𝚈 𝚌𝚘𝚕𝚜). See example 2. What does 𝚁𝙰𝙽𝙺 𝙾𝚅𝙴𝚁(𝙿𝙰𝚁𝚃𝙸𝚃𝙸𝙾𝙽 𝙱𝚈 𝚌𝚘𝚕𝚜) do? 👉 divides the table into multiple partitions, one for each unique value

SQL

Create Running Weighted Average

Combine ROW_NUMBER, self JOIN, and CASE WHEN for that

SQL

Find Year-on-Year Growth Rate

This is a common interview question that you MUST nail! The formula is (value - last_year_value) / last_year_value. You can calculate last_year_value as 📌 𝙻𝙰𝙶(𝚟𝚊𝚕𝚞𝚎, 𝟷) 𝚘𝚟𝚎𝚛 (𝚘𝚛𝚍𝚎𝚛 𝚋𝚢 𝚟𝚊𝚕𝚞𝚎) 𝙻𝙰𝙶(𝚟𝚊𝚕𝚞𝚎,𝟷) gets the value from the previous row. 𝙻𝙰𝙶(𝚟𝚊𝚕𝚞𝚎,𝟸) gets the value from the row before the previous row. STEPS: 1️⃣ Use 𝙻𝙰𝙶 to get

SQL

Use EXISTS to check if a row is present

Need to find if records from table A can also be found in table B? EXISTS is here to help. 📌 Here's the syntax. 𝚂𝙴𝙻𝙴𝙲𝚃 * 𝙵𝚁𝙾𝙼 𝚝𝚊𝚋𝚕𝚎_𝚊 𝚆𝙷𝙴𝚁𝙴 𝙴𝚇𝙸𝚂𝚃𝚂 ( 𝚂𝙴𝙻𝙴𝙲𝚃 * 𝙵𝚁𝙾𝙼 𝚝𝚊𝚋𝚕𝚎_𝚋 𝚆𝙷𝙴𝚁𝙴 𝚝𝚊𝚋𝚕𝚎_𝚊.𝚔𝚎𝚢 = 𝚝𝚊𝚋𝚕𝚎_𝚋.𝚔𝚎𝚢 ) 𝚒𝚗𝚗𝚎𝚛_𝚚𝚞𝚎𝚛𝚢 Let's walk through this. 1️⃣ the inner query retrieves all the records from table_b that has a common key with table_a.

SQL

The SIGN function

Use SIGN to see if a number is positive, negative, or zero. It's as simple as it sounds. Apply SIGN() to an integer or float column to get: - SIGN(0) gives 0 - SIGN(POSITIVE_NUM) gives 1 - SIGN(NEGATIVE_NUM) gives -1 Caution! Be careful

SQL

Use SAFE_DIVIDE to handle division by zero.

𝙎𝘼𝙁𝙀_𝘿𝙄𝙑𝙄𝘿𝙀 divides two numbers while handling the case where the denominator is zero. 📌 Here's the syntax. 𝚂𝙴𝙻𝙴𝙲𝚃 𝚂𝙰𝙵𝙴_𝙳𝙸𝚅𝙸𝙳𝙴(𝚗𝚞𝚖𝚎𝚛𝚊𝚝𝚘𝚛, 𝚍𝚎𝚗𝚘𝚖𝚒𝚗𝚊𝚝𝚘𝚛) 𝙵𝚁𝙾𝙼 𝚗𝚞𝚖𝚋𝚎𝚛𝚜 If the denominator is zero for any row, it returns NULL instead of causing your query to fail. 𝙎𝘼𝙁𝙀_𝘿𝙄𝙑𝙄𝘿𝙀 is not part of the standard SQL language, but it is often included as a

SQL

CUBE+GROUP BY for Subtotals

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

SQL

Get subtotals with ROLLUP+GROUP BY

𝙍𝙊𝙇𝙇𝙐𝙋 generates extra rows that represent the subtotals. For example… To get the sum of spending of each user, you typically GROUP BY user_id and SUM spend. Want to get the sum of ALL users in the same result? Try ROLLUP. 📌 The syntax is here. 𝚂𝙴𝙻𝙴𝙲𝚃 𝚄𝚜𝚎𝚛_𝚒𝚍, 𝚂𝚄𝙼(𝚜𝚙𝚎𝚗𝚍) 𝙵𝚁𝙾𝙼 𝚄𝚜𝚎𝚛𝚜 𝙶𝚁𝙾𝚄𝙿 𝙱𝚈 𝚁𝙾𝙻𝙻𝚄𝙿(𝚞𝚜𝚎𝚛_𝚒𝚍) The result will contain a

SQL

CROSS JOIN for all combinations

It is the cartesian products of both tables, if you're a math nerd. 📌 Syntax: 𝚂𝙴𝙻𝙴𝙲𝚃 * 𝙵𝚁𝙾𝙼 𝚝𝚊𝚋𝚕𝚎_𝟷 𝙲𝚁𝙾𝚂𝚂 𝙹𝙾𝙸𝙽 𝚝𝚊𝚋𝚕𝚎_𝟸 Note that there is no need for an ON clause. CROSS JOIN is useful for getting all combinations - Matching users with all dates. - Match product tables with color tables to get product