Common SQL fragments

Time:2022-6-19

Data analysis is a skill. No matter what position it is, there are always extra points. Everything is for the data. It is more advantageous to find opportunities from the data.

The following records some useful SQL fragments:

Create temporary table

From subquery = > create temporary table query

When there are too many subqueries, using this method can make the SQL clearer

SELECT name
       , salary
FROM People
WHERE name in (SELECT DISTINCT name 
               FROM population 
               WHERE country = "Canada"
                     AND city = "Toronto")
      AND salary >= (SELECT AVG(salary)
                     FROM salaries
                     WHERE gender = "Female")
                     

--Temporary table query
with toronto_ppl as (
   SELECT DISTINCT name
   FROM population
   WHERE country = "Canada"
         AND city = "Toronto"
)
, avg_female_salary as (
   SELECT AVG(salary) as avgSalary
   FROM salaries
   WHERE gender = "Female"
)
SELECT name
       , salary
FROM People
WHERE name in (SELECT DISTINCT FROM toronto_ppl)
      AND salary >= (SELECT avgSalary FROM avg_female_salary)

Temporary function

Just like writing code, some repeated input and output are extracted as a temporary function to make the code clearer.

SELECT name
       , CASE WHEN tenure < 1 THEN "analyst"
              WHEN tenure BETWEEN 1 and 3 THEN "associate"
              WHEN tenure BETWEEN 3 and 5 THEN "senior"
              WHEN tenure > 5 THEN "vp"
              ELSE "n/a"
         END AS seniority 
FROM employees

--Temporary function
CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) AS (
   CASE WHEN tenure < 1 THEN "analyst"
        WHEN tenure BETWEEN 1 and 3 THEN "associate"
        WHEN tenure BETWEEN 3 and 5 THEN "senior"
        WHEN tenure > 5 THEN "vp"
        ELSE "n/a"
   END
);
SELECT name
       , get_seniority(tenure) as seniority
FROM employees

Add columns and make wide tables

Add case when according to conditions
SELECT name
       , case when age < 18 then 'minor' end as desc1
             , case when city in ('injury ') then' first tier cities' end as desc2
             ,..
FROM employees

Sort row_ number rank dense_ rank

SELECT Name
       , GPA
       , ROW_NUMBER() OVER (ORDER BY GPA desc)
       , RANK() OVER (ORDER BY GPA desc)
       , DENSE_RANK() OVER (ORDER BY GPA desc)
FROM student_grades
Common SQL fragments

image-20211115081901264

Calculate increment

Required when comparing values of different periods

# Comparing each month's sales to last month
SELECT month
       , sales
       , sales - LAG(sales, 1) OVER (ORDER BY month)
FROM monthly_sales

# Comparing each month's sales to the same month last year
SELECT month
       , sales
       , sales - LAG(sales, 12) OVER (ORDER BY month)
FROM monthly_sales

Calculate cumulative curve

SELECT Month
       , Revenue
       , SUM(Revenue) OVER (ORDER BY Month) AS Cumulative
FROM monthly_revenue
Common SQL fragments

image-20211115081750800

https://towardsdatascience.com/ten-advanced-sql-concepts-you-should-know-for-data-science-interviews-4d7015ec74b0