+- Forums (https://bdn.bdb.ai)
+-- Forum: BDB Knowledge Base (https://bdn.bdb.ai/forumdisplay.php?fid=13)
+--- Forum: BDB Dashboards Designer (https://bdn.bdb.ai/forumdisplay.php?fid=43)
+---- Forum: BDB Designer Q & A (https://bdn.bdb.ai/forumdisplay.php?fid=16)
+---- Thread: Best practice to write SQL queries (/showthread.php?tid=499)
Best practice to write SQL queries - ArghaPratim - 12-23-2022
· 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