MASTERING
SQL FOR
DATA ANALYTICS: Rudraksh Atreya
The Ultimate
Advanced
Cheatsheet for
Data Analytics
and
Interview
Excellence
1. WINDOW
FUNCTIONS:
Rudraksh Atreya
SQL
SELECT column1, column2,
function_name()
OVER (PARTITION BY
partition_column
ORDER BY order_column) AS
alias_name
FROM table_name
2. SUBQUERIES:
Rudraksh Atreya
SQL
SELECT column1, column2
FROM table_name
WHERE column_name IN
(
SELECT column_name FROM
another_table )
3. CTE (COMMON
TABLE
EXPRESSION): Rudraksh Atreya
SQL
WITH cte_name AS (
SELECT column1, column2
FROM table_name
)
SELECT column1, column2
FROM cte_name
4. CASE
STATEMENTS:
Rudraksh Atreya
SQL
SELECT column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS alias_name
FROM table_name
5. AGGREGATE
FUNCTIONS
WITH GROUP BY
Rudraksh Atreya
ROLLUP/CUBE:
SQL
SELECT column1, column2,
SUM(column3)
FROM table_name
GROUP BY ROLLUP
(column1, column2)
6. PIVOT:
Rudraksh Atreya
SQL
SELECT *
FROM table_name
PIVOT (
aggregate_function (column1)
FOR column2 IN ([value1],
[value2], [value3])
) AS alias_name
7. UNPIVOT:
Rudraksh Atreya
SQL
SELECT column1, column2
FROM table_name
UNPIVOT (
column2
FOR column1 IN
([value1], [value2], [value3])
) AS alias_name
8. TEMPORARY
TABLES:
Rudraksh Atreya
SQL
CREATE
TEMPORARY TABLE
temp_table_name
AS
SELECT column1, column2
FROM table_name
9. ANALYTIC
FUNCTIONS:
Rudraksh Atreya
SQL
SELECT column1, column2,
analytic_function() OVER
(PARTITION BY partition_column
ORDER BY order_column)
AS alias_name
FROM table_name
10. INDEXING:
Rudraksh Atreya
SQL
CREATE INDEX
index_name
ON
table_name
(
column_name
)
11. MATERIALIZED
VIEWS:
Rudraksh Atreya
SQL
CREATE
MATERIALIZED VIEW
view_name
AS
SELECT column1, column2
FROM
table_name
