SQL FAQ

 		# output : 
			 a | b | c1 | c2 | d
			 a1  b1  c11  c21   d1
			 a2 b2  c12  c22   d2
			 a3  b3  c13  c23   d3
			 .......             

		# output :   
		  	  col1 | col2
		  	   a1     b1
		  	   a2     b2
		  	   c1     d2
		  	   c2     d2
		  	   ..........

-- postgre

SELECT extract(QUARTER
               FROM order_timestamp) AS QUARTER,
       count(*)
FROM orders 
GROUP BY 1;

-- mysql

SELECT QUARTER(order_timestamp) AS QUARTER,
       count(*)
FROM orders 
GROUP BY 1;

-- postgre

SELECT commit_timestamp,
       max(commit_timestamp) max_commit_time,

  (SELECT max(commit_timestamp)
   FROM git_commit
   WHERE commit_timestamp >= '2019-01-01'
     AND commit_timestamp <= '2019-12-31' ) AS max_commit_time_in_timeslot
FROM git_commit
GROUP BY 1
LIMIT 10 ;

-- postgre
-- V1

WITH user_commit_count AS
  (SELECT user_id,
          count(*) AS commit_count
   FROM git_commit
   GROUP BY 1),
     avg_commit AS
  (SELECT avg(commit_count)::int AS avg_commit_count
   FROM user_commit_count)
SELECT user_id,
       commit_count,
  (SELECT *
   FROM avg_commit)
FROM user_commit_count
WHERE commit_count >
    (SELECT avg_commit_count
     FROM avg_commit)
LIMIT 30;

-- postgre

WITH RECURSIVE my_num AS
  (SELECT 1 AS seqnum
   UNION ALL SELECT seqnum + 1
   FROM my_num
   WHERE seqnum < 100 )
SELECT seqnum
FROM my_num;

CREATE TABLE movie (
  movie_id   INT PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  release_yr SMALLINT
);

CREATE TABLE actor (
  actor_id INT PRIMARY KEY,
  name     VARCHAR(200) NOT NULL
);

-- the junction table: one row per (movie, actor) pair
CREATE TABLE movie_actor (
  movie_id INT NOT NULL REFERENCES movie(movie_id),
  actor_id INT NOT NULL REFERENCES actor(actor_id),
  role     VARCHAR(200),                 -- attributes OF THE RELATIONSHIP live here
  PRIMARY KEY (movie_id, actor_id)       -- composite PK = dedupe + the forward index
);

-- serves the reverse lookup "which films did this actor appear in?"
-- (a separate statement, so this DDL runs on PostgreSQL as well as MySQL)
CREATE INDEX idx_movie_actor_actor ON movie_actor (actor_id, movie_id);

-- number of actors for a given movie id
SELECT COUNT(*) AS actor_cnt
FROM   movie_actor
WHERE  movie_id = 123;                       -- index-only: no join needed

-- ... by movie name
SELECT m.movie_id, m.name, COUNT(ma.actor_id) AS actor_cnt
FROM   movie m
LEFT   JOIN movie_actor ma ON ma.movie_id = m.movie_id   -- LEFT JOIN -> movies with 0 actors still report 0
WHERE  m.name = 'The Matrix'
GROUP  BY m.movie_id, m.name;

# mysql
SELECT 
@x AS x, 
@y AS y, 
@x + 1 AS x_Plus_1, 
@x := @x + 1 AS updated_x
FROM
  (SELECT @x := 0, @y := 1) INIT;

DELETE a
FROM TABLE a,
           TABLE b
WHERE a.id = b.id
  AND a.timestamp > b.timestamp

-- build the table 
CREATE TABLE IF NOT EXISTS test( id int, age int);
TRUNCATE TABLE test; 
INSERT INTO test VALUES (1,1);
INSERT INTO test VALUES (2,2);
INSERT INTO test VALUES (3,3);
INSERT INTO test VALUES (3,3);
INSERT INTO test VALUES (3,3);
INSERT INTO test VALUES (3,3);
SELECT * FROM test;

-- delete duplicated
-- V1 
DELETE
FROM test
WHERE id IN
    (SELECT id
     FROM
       (SELECT 
        id,
        age,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS order_
        FROM test) sub
     WHERE order_ > 2 )

-- delete duplicated
-- V2
DELETE FROM test
WHERE id IN (
  SELECT calc_id FROM (
    SELECT MAX(id) AS calc_id
    FROM test
    GROUP BY id, age
    HAVING COUNT(id) > 1
  ) temp
); 

-- select data where SALARY is not null
 SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM CUSTOMERS
   WHERE SALARY IS NOT NULL; 

-- select data where SALARY is null
 SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM CUSTOMERS
   WHERE SALARY IS NULL; 

-- COALESCE
SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result
FROM StudentDetails

-- above SQL is as same as below 
-- i.e. COALESCE(a, b, c, 0)
--  -> if a is not null then a 
--      -> if b is not null then b
--          -> if c is not null then c 
--                -> else 0 
SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result,
CASE
  WHEN Semester_I IS NOT NULL THEN Semester_I
  WHEN Semester_II IS NOT NULL THEN Semester_II
  WHEN Semester_III IS NOT NULL THEN Semester_III
  ELSE 0
END CASE_Result
FROM StudentDetails

-- Method 1  : IFNULL
SELECT IFNULL(NULL, 0) + value; 

-- Method 2  : COALESCE
SELECT COALESCE(NULL, value); 

SELECT NULL + 100;
-- NULL 

SELECT NULL != NULL;
-- NULL 

SELECT NULL = NULL;
-- NULL 

-- sub-query pattern
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

SELECT
aggre_func() OVER (PARTITION BY ... ORDER BY ...),
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...),
RANK() OVER (PARTITION BY ... ORDER BY ...),
LAG(timestamp, 1) OVER (PARTITION BY ... ORDER BY ...) AS prev_record,
RANK() OVER (PARTITION BY timestamp, ORDER BY count(product_id)) AS rank # NOTE : rank can order by count
...

-- example 1 
SELECT duration_seconds,
       SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total
  FROM tutorial.dc_bikeshare_q1_2012

-- example 2 
SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_total
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'

-- example 3 
SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY start_terminal) AS running_total,
       COUNT(duration_seconds) OVER
         (PARTITION BY start_terminal) AS running_count,
       AVG(duration_seconds) OVER
         (PARTITION BY start_terminal) AS running_avg
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'

SELECT start_terminal,
       start_time,
       duration_seconds,
       ROW_NUMBER() OVER (PARTITION BY start_terminal
                          ORDER BY start_time)
                    AS row_number
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'

SELECT start_terminal,
       duration_seconds,
       RANK() OVER (PARTITION BY start_terminal
                    ORDER BY start_time)
              AS rank
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'

-- example 1 
SELECT start_terminal,
       duration_seconds,
       LAG(duration_seconds, 1) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds) AS lag,
       LEAD(duration_seconds, 1) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds) AS lead
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds

-- example 2 
SELECT start_terminal,
       duration_seconds,
       duration_seconds -LAG(duration_seconds, 1) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
         AS difference
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds

SELECT start_terminal,
       duration_seconds,
       NTILE(4) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
          AS quartile,
       NTILE(5) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
         AS quintile,
       NTILE(100) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
         AS percentile
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds

SELECT start_terminal,
       duration_seconds,
       NTILE(4) OVER ntile_window AS quartile,
       NTILE(5) OVER ntile_window AS quintile,
       NTILE(100) OVER ntile_window AS percentile
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
WINDOW ntile_window AS
         (PARTITION BY start_terminal ORDER BY duration_seconds)
 ORDER BY start_terminal, duration_seconds

-- build table 
CREATE table if not exists user(id int, age int); 
truncate table user; 
INSERT INTO user values (1, 10);
INSERT INTO user values (1, 10);
INSERT INTO user values (1, 10);
INSERT INTO user values (1, 10);
INSERT INTO user values (1, 10);
INSERT INTO user values (NULL, 10);
INSERT INTO user values (1, NULL);

-- select 
SELECT * FROM user;

-- mysql> SELECT * FROM user;
-- +------+------+
-- | id   | age  |
-- +------+------+
-- |    1 |   10 |
-- |    1 |   10 |
-- |    1 |   10 |
-- |    1 |   10 |
-- |    1 |   10 |
-- | NULL |   10 |
-- |    1 | NULL |
-- +------+------+
-- 7 rows in set (0.00 sec)

-- select 
SELECT COUNT(id), COUNT(*) ,COUNT(1) FROM user;

-- mysql> SELECT COUNT(id), COUNT(*) ,COUNT(1) FROM user;
-- +-----------+----------+----------+
-- | COUNT(id) | COUNT(*) | COUNT(1) |
-- +-----------+----------+----------+
-- |         6 |        7 |        7 |
-- +-----------+----------+----------+
-- 1 row in set (0.00 sec)

-- example 1 
SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,
Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME HAVING AVG_SALARY > 7000;

-- example 2 
update DEPARTMENT set DEPT_NAME="NewSales" WHERE DEPT_ID=1 ; 

CASE expression   
    WHEN expression_1 THEN result_1
    WHEN expression_2 THEN result_2
    ...
    WHEN expression_n THEN result_n
    [ ELSE else_result ]   
END  

SELECT 
    title, 
    rating,
    CASE 
        WHEN (rating >= 1 AND rating < 2) THEN 'Not so good' 
        WHEN (rating >= 2 AND rating < 3) THEN 'Limited useful information'
        WHEN (rating >= 3 AND rating < 4) THEN 'Good book, but nothing special'
        WHEN (rating >= 4 AND rating < 5) THEN 'Incredbly special'
        WHEN rating = 5 THEN 'Life changing. Must Read.'
        ELSE
            'No rating yet'
    END AS comment
FROM 
    books
ORDER BY 
    title;

1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()

SELECT COUNT()
SELECT Count(Distinct Salary)
SELECT Avg(salary)  --  Sum(salary) / count(salary) = 310/5
SELECT Avg(Distinct salary) -- = sum(Distinct salary) / Count(Distinct Salary) 

CREATE TABLE employee (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(100),
  manager_id INT REFERENCES employee(emp_id)   -- self reference; NULL for the CEO
);

-- everyone up to 10 levels under manager 1, with their depth in the tree
WITH RECURSIVE org AS (

    -- 1) ANCHOR: where the walk starts
    SELECT emp_id, name, manager_id, 1 AS lvl, CAST(name AS CHAR(1000)) AS path
    FROM   employee
    WHERE  emp_id = 1

    UNION ALL

    -- 2) RECURSIVE part: join the table back onto the rows found so far
    SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1, CONCAT(o.path, ' > ', e.name)
    FROM   employee e
    JOIN   org o ON e.manager_id = o.emp_id
    WHERE  o.lvl < 10                      -- depth guard: a cycle otherwise loops forever
)
SELECT lvl, path FROM org ORDER BY path;

lvl | path
----+---------------------------
  1 | Ada
  2 | Ada > Brian
  3 | Ada > Brian > Chen
  2 | Ada > Dara

WITH recursive cte(columns_name) 
AS (

  initial_query
  UNION ALL
  recursive_query

)
SELECT * 
FROM cte

# https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-traditional?view=sql-server-ver15
# Not Equal To (Transact SQL)
# Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL, see the topic SET ANSI_NULLS (Transact-SQL).
expression <> expression  

# LC 1790 : Biggest Window Between Visits
WITH cte1 AS
  (SELECT user_id,
          coalesce(lead(visit_date) OVER (PARTITION BY user_id
                                          ORDER BY visit_date), '2021-01-01') AS lead_visit_date,
          visit_date
   FROM UserVisits),
     cte2 AS
  (SELECT user_id,
          max(datediff(lead_visit_date, visit_date)) AS max_diff
   FROM cte1
   GROUP BY user_id)
SELECT *
FROM cte2
ORDER BY user_id

# LC 1831
WITH cte AS
  (SELECT transaction_id,
          RANK() OVER (PARTITION BY DATE(DAY)
                       ORDER BY amount DESC) AS rank
   FROM transactions)
SELECT transaction_id
FROM cte
WHERE rank = 1
ORDER BY transaction_id

# V1
SELECT * FROM table_a
WHERE EXISTS
(SELECT * FROM table_b WHERE table_b.id=table_a.id);

# is equal to below

# V2
SELECT * FROM table_a
WHERE id
in (SELECT id FROM table_b);

# sql 1
SELECT table_column1, table_column2...
FROM table_name1
CROSS JOIN table_name2;

# sql 2
SELECT table_column1, table_column2...
FROM table_name1, table_name2;

# sql 3
SELECT table_column1, table_column2...
FROM table_name1
JOIN table_name2;

# LC 0183
### NOTE : 
#       -> SHOULD BE o.CustomerId is NULL
#       -> RATHER THAN o.CustomerId = NULL
SELECT
c.Name AS Customers
FROM
Customers c
left join
Orders o
on c.Id = o.CustomerId
WHERE
o.CustomerId is NULL -- note this condition

-- LC 184 Department Highest Salary
/* V0 */
SELECT d.name AS Department,
       e.name AS Employee,
       e.salary AS Salary
FROM Employee e
INNER JOIN Department d ON e.DepartmentId = d.Id
--  NOTE : below where has 2 columns !!!
WHERE (e.DepartmentId,
       e.salary) in
    (SELECT DepartmentId,
            max(Salary) AS salary
     FROM Employee GROUP
BY DepartmentId)

-- data
| user_id | date       |
|---------|------------|
| 1       | 2018-07-01 |
| 234     | 2018-07-02 |
| 3       | 2018-07-02 |
| 1       | 2018-07-02 |
| ...     | ...        |
| 234     | 2018-10-04 |

WITH mau AS 
(
  SELECT 
   /* 
    * Typically, interviewers allow you to write psuedocode for date functions 
    * i.e. will NOT be checking if you have memorized date functions. 
    * Just explain what your function does as you whiteboard 
    *
    * DATE_TRUNC() is available in Postgres, but other SQL date functions or 
    * combinations of date functions can give you a identical results   
    * See https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
    */ 
    DATE_TRUNC('month', date) month_timestamp,
    COUNT(DISTINCT user_id) mau
  FROM 
    logins 
  GROUP BY 
    DATE_TRUNC('month', date)
  )
 
 SELECT 
    /*
    * You don't literally need to include the previous month in this SELECT statement. 
    * 
    * However, as mentioned in the "Tips" section of this guide, it can be helpful 
    * to at least sketch out self-joins to avoid getting confused which table 
    * represents the prior month vs current month, etc. 
    */ 
    a.month_timestamp previous_month, 
    a.mau previous_mau, 
    b.month_timestamp current_month, 
    b.mau current_mau, 
    ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change 
 FROM
    mau a 
 JOIN 
    /*
    * Could also have done `ON b.month_timestamp = a.month_timestamp + interval '1 month'` 
    */
    mau b ON a.month_timestamp = b.month_timestamp - interval '1 month' 

-- LC 580 Count Student Number in Departments
# V1
# https://www.datageekinme.com/general/leetcode/leetcode-sql-580-count-student-number-in-departments/
select 
    a.dept_name,
    coalesce(count(student_id), 0) student_number
from 
    department a 
left join
    student b
on 
    (a.dept_id = b.dept_id)
group by a.dept_name
order by student_number desc, a.dept_name asc; --- NOTE this !!!