I’m a product data analyst
This is my peripherical-brain for sql, it’s not exhaustive, but I try to update it with cool stuff I learned along the way.

Started: ~2015


SQL 🛢️

Ressources


___


row_number() vs rank() vs dense_rank() 

SELECT
  v,
  ROW_NUMBER() OVER (ORDER BY v) row_number,
  RANK()       OVER (ORDER BY v) rank,
  DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| p |          1 |    1 |          1 |
| p |          2 |    1 |          1 |
| p |          3 |    1 |          1 |
| q |          4 |    4 |          2 |
| r |          5 |    5 |          3 |
| r |          6 |    5 |          3 |
| s |          7 |    7 |          4 |
| t |          8 |    8 |          5 |
+---+------------+------+------------+

Update the patients table for the allergies column. If the patient’s allergies is null then replace it with ‘NKA’ 

UPDATE patients
SET allergies = 'NKA'
WHERE allergies IS null;

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.


#WIP