The SQL InterviewCheatsheet

to ace your next Data Analytics interview

Follow Amney Mounir for more data Analytics tips and resources

1. Basic sQL Queries 3.Grouping LEFT JOIN 7. String Functions

SELECT
Selectall data Group by and count Using CONCATENATE
t1.column 2

SELECT FROM table

SELECT column_1, COUNT(") t2.column_1
Combine multiple strings together

FROM table FROM tablel t1 SELECT

Select specific columns GROUP BY 1 LEFT JOIN table2 t2 cONCATENATE(Column 1, column_2)

SELECT Columnl, column2 FROM table ON t1.column_1 =t2.column_2 FROM table

Group by and sum

Filter by condition SELECT Column 1, SUM(column SELF JOIN
SELECT
2)
String LENGTH
FROM table FROM table SELECT Get the lenght of any specific column
WHEREColumn 1=value GROUPBY 1
t1.column_1 AS parent, SELECT LENGTH(Column 1) FROM table

t2.column_1AS child
Sort Records HAVING
Using FROM tablel tl
SELECT* FROM table SUBSTRING
SELECT column_1, SUM(column_2) LEFT JOIN tablel t2
ORDER BY column 1
Extract a portion of a given starting given a start
FROM table ON tl.column_1=t2.column_2 and end position.
GROUPBY
SELECT
Filter by Range HAVING SUM(Column_2)>5 CROSS JOIN TRING(Column_1, 1,3)
SELECT* FROM table
SELECT FROM table
WHERE column l BETWEEN 1 AND 5O 4. Subgueries & CTE tl.column

1,
Find null values WHERE t2.column 2, Upper and Lower case
Subqueries in
t2.column 3, Display a given string in lower or upper case
rFROM table SELECT* FROM table_ 1
FROM tablel t1
SELECT
SELEumn
WHERE iS NULL WHERE Column_1 IN (SELECT CROSS JOIN tablelt2
1
UPPER(Column 1), LOWER(Column 2)
MAX(column 1)FROM table 2
FROM table
Remove Duplicate Values
SELECT DISTINCTColumn_1 Using WITH 6. Ranking Functions
FROM table
8. Date Functions
WITH my_table AS ( Using RANK
SELECT Assigns the same rank for duplcates but skips numbers Using CURRENT DATE
2. Aggregation column_ 1, SELECT Set date as of today

Count Records cOUNT()ASnb_users column1, column_2 SELECT* FROM table

FROM table RANK) OVER(ORDER BY column_1DESC) WHERE Column_date = CURRENT_DATE
SELECT COUNT(") FROM table
GROUP BY 1) FROM table

Sum Column Values

Using DENSE RANK EXTRACT year
SELECT nb_users, COUNTI") Get the year of any date column
SELECT SUM(Column1) FROM table Assigns the same rank far duplicates but does not
FROM my_table
skip numbers. from column_date)
GROUP BY 1 SELECT EXTRACT(year
Finding the Maximum value FROM table
SELECT
SELECT MAX(column_1) column_l,column_2
FROM table 5. Joins DENSE _RANK) OVER(ORDER BY column_2) DATE DIFF
FROM table
Extract the ditference between 2dates
Finding the Minimum value INNER JOIN SELECT

SELECT MIN(column_1)
FROM table
SELECT Using ROW NUMBER DATE_ DIFF('day',
FROM table
column_dtl, column_dt 2)

t1.column 2. Assigns a unique number, even for duplicates

t2.column_1 SELECT
Calculating Average FROM tablel t1
INTERVAL
column 1, column_2 Add interval date to a date column
SELECT AVG(column _1) INNER JOIN table2 t2 ROW_NUMBER)OVER(ORDER BY column_2)
SELECT * FROM table
FROM table ON tI.column_l=t2.column_2 FROM table
WHERE dt> dt2+ INTERVAL'1' DAY