4. CTE > Sub-queries

In most situations you want to use a CTE (common table expression) instead of a sub-query.

5. Using SUM and CASE WHEN Together

select 
     sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
   , sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end)   as allergies_oak

from patients

Start where clause with true

where 1=1
and ...
and ...
and...

7. Don’t Forget About Window Functions

select
    p.*
  , MAX(weight) over (partition by city) as maxwt_by_city
 from patients p

Window functions are a great way to keep all of the data rows and then append another column with important aggregate details.