Best practice to write SQL queries

[ArghaPratim]

·      Use Uppercase for the Keywords
Avoid
 
select id, name from company.customers
 
Prefer
 
SELECT id, name FROM company.customers
 
·      Use Snake Case for the schemas, tables, columns
Avoid
 
SELECT Customers.id,
       Customers.name,
       COUNT(WebVisit.id) as nbVisit
FROM COMPANY.Customers
JOIN COMPANY.WebVisit ON Customers.id = WebVisit.customerId
WHERE Customers.age <= 30
GROUP BY Customers.id, Customers.name
Prefer
 
SELECT customers.id,
       customers.name,
       COUNT(web_visit.id) as nb_visit
FROM company.customers
JOIN company.web_visit ON customers.id = web_visit.customer_id
WHERE customers.age <= 30
GROUP BY customers.id, customers.name
 
·      Use aliases when it improves readability
Avoid
 
SELECT customers.id,
       customers.name,
       customers.context_col1,
       nested.f0_
FROM company.customers
JOIN (
          SELECT customer_id,
                 MIN(date)
          FROM company.purchases
          GROUP BY customer_id
      ) ON customer_id = customers.id
Prefer
 
SELECT customers.id,
       customers.name,
       customers.context_col1 as ip_address,
       first_purchase.date    as first_purchase_date
FROM company.customers
JOIN (
          SELECT customer_id,
                 MIN(date) as date
          FROM company.purchases
          GROUP BY customer_id
      ) AS first_purchase
        ON first_purchase.customer_id = customers.id
·      Formatting: Carefully use Indentation & White spaces
Avoid
SELECT customers.id, customers.name, customers.age, customers.gender, customers.salary, first_purchase.date
FROM company.customers
LEFT JOIN ( SELECT customer_id, MIN(date) as date FROM company.purchases GROUP BY customer_id ) AS first_purchase
ON first_purchase.customer_id = customers.id
WHERE customers.age<=30
 
Prefer
 
SELECT customers.id,
       customers.name,
       customers.age,
       customers.gender,
       customers.salary,
       first_purchase.date
FROM company.customers
LEFT JOIN (
              SELECT customer_id,
                     MIN(date) as date
              FROM company.purchases
              GROUP BY customer_id
          ) AS first_purchase
            ON first_purchase.customer_id = customers.id
WHERE customers.age <= 30
·      Avoid Select *
Avoid
 
SELECT * EXCEPT(id) FROM company.customers
Prefer
 
SELECT name,
       age,
       salary
FROM company.customers
·      Go for the JOIN Syntax
Avoid
 
SELECT customers.id,
       customers.name,
       COUNT(transactions.id) as nb_transaction
FROM company.customers, company.transactions
WHERE customers.id = transactions.customer_id
      AND customers.age <= 30
GROUP BY customers.id, customers.name
Prefer
 
SELECT customers.id,
       customers.name,
       COUNT(transactions.id) as nb_transaction
FROM company.customers
JOIN company.transactions ON customers.id = transactions.customer_id
WHERE customers.age <= 30
GROUP BY customers.id, customers.name
 
·      Sometimes, it might be worth splitting into multiple queries
Instead Of
 
CREATE TABLE customers_infos AS
SELECT customers.id,
       customers.salary,
       traffic_info.weeks_since_last_visit,
       category_info.most_visited_category_id,
       purchase_info.highest_purchase_value
FROM company.customers
LEFT JOIN ([..]) AS traffic_info
LEFT JOIN ([..]) AS category_info
LEFT JOIN ([..]) AS purchase_info
You Could Use
 
## STEP1: Create initial table
CREATE TABLE public.customers_infos AS
SELECT customers.id,
       customers.salary,
       0 as weeks_since_last_visit,
       0 as most_visited_category_id,
       0 as highest_purchase_value
FROM company.customers
## STEP2: Update traffic infos
UPDATE public.customers_infos
SET weeks_since_last_visit = DATE_DIFF(CURRENT_DATE,
                                       last_visit.date, WEEK)
FROM (
         SELECT customer_id, max(visit_date) as date
         FROM web.traffic_info
         GROUP BY customer_id
     ) AS last_visit
WHERE last_visit.customer_id = customers_infos.id
## STEP3: Update category infos
UPDATE public.customers_infos
SET most_visited_category_id = [...]
WHERE [...]
## STEP4: Update purchase infos
UPDATE public.customers_infos
SET highest_purchase_value = [...]
WHERE [...]
·      Meaningful names based on your own conventions
              
·      Finally, write useful comments… but not too much