Travis Tang

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

Python

Lazypredict: Run All Sklearn Algorithms With a Line Of Code

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

SQL

Generate Histogram Distribution

For a quick exploration of the distribution

Python

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.

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

Python

4x Faster Pandas Operations with Minimal Code Change

Stop waiting on pandas operations. Parallelize them.

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