This week, the goal had been to level up my SQL skills on Codewars. In one week, I’ve gotten to 5 kyu for SQL. These are the notes I’ve noticed from solving problems.

Missing Values In SQL

Substitute Missing Data

SELECT GDP, Country, 
ISNULL(Country, 'Unknown') AS NewCountry
FROM EconomicIndicators;

Substitute Missing Data With Values From Another Column

SELECT TradeGDPPercent, ImportGoodPercent,
ISNULL(TradeGDPPercent, ImportGoodPercent) AS NewPercent
FROM EconomicIndicators;

COALESCE

• Uses the first value in row to determine what is missing value in column.
• If all preceding columns in row are Null, then the last value in COALESCE become the value in cell
• NULLIF() turns empty row into null, use COALESCE to insert value
  • COALESCE(NULLIF({col}, ”), {new_value for blank})

SELECT TradeGDPPercent, ImportGoodPercent, 
COALESCE(TradeGDPPercent, ImportGoodPercent, 'N/A') AS NewPercent
FROM EconomicIndicators;

Dates

DATEDIFF()

• Syntax: DATEDIFF(interval, date1, date2
• Intervals:
  • year, yyyy, yy = Year
  • quarter, qq, q = Quarter
  • month, mm, m = month
  • dayofyear = Day of the year
  • day, dy, y = Day
  • week, ww, wk = Week
  • weekday, dw, w = Weekday
  • hour, hh = hour
  • minute, mi, n = Minute
  • second, ss, s = Second
  • millisecond, ms = Milliseconds

SELECT OrderDate, ShipDate,
    DATEDIFF(DD, OrderDate, ShipDate) AS Duration
FROM Shipments;

DATEADD()

• Syntax DATEADD(interval, number, date)
• Interval:
  • year, yyyy, yy = Year
  • quarter, qq, q = Quarter
  • month, mm, m = month
  • dayofyear, dy, y = Day of the year
  • day, dd, d = Day
  • week, ww, wk = Week
  • weekday, dw, w = Weekday
  • hour, hh = hour
  • minute, mi, n = Minute
  • second, ss, s = Second
  • millisecond, ms = Millisecond

SELECT OrderDate, 
   ShipDate, 
    DATEADD(DD, 5, ShipDate) As DeliveryDate
   FROM Shipments;

Math Values

• absolute value: ABS()
• square: SQUARE()
• square root: SQRT()
• natural log: LOG(number, base)

Running Total

• Syntax: SUM({column}) OVER (ORDER BY {column1})
• SUM(COUNT(created_at)) OVER (ORDER BY created_at::DATE)::INT AS total

-- create running total from count
SELECT
  CREATED_AT::DATE AS DATE,
  COUNT(CREATED_AT) AS COUNT,
  SUM(COUNT(CREATED_AT)) OVER (ORDER BY CREATED_AT::DATE ROWS UNBOUNDED PRECEDING)::INT AS TOTAL
FROM
  POSTS
GROUP BY
  CREATED_AT::DATE

Rounding Numbers

• Syntax: ROUND(number, decimals, operation])
  • Positive decimals is to the right of decimal
  • Negative decimals is rounding to the left of decimal
• Operation (truncate to decimal places)
  • 0: rounds to the number of decimal
  • n: truncates result to the number of decimals

SELECT Cost, 
   ROUND(Cost, 0, 1) AS RoundCost -- trunncates to one decimal place
FROM Shipments;

RANK()

• syntax: RANK() OVER (ORDER BY salary) salary_rank

Convert Columns To Type

Convert()

• Syntax: CONVERT(data_type(length), expression, style)
• data_type (required):
  • bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
• expression (required): The value to convert to another data type

Cast()

• Syntax: CAST(expression AS datatype(length))
• datatype:
  • bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image

In Mathematical Format (::)

• sum(p.amount::float)

Strings

UPPER(), LOWER(), INITCAP()

• use INITCAP() similar to title() in python
• INITCAP() capitalizes each word separated by a separator

Concat With Space

• syntax: CONCAT_WS(separator, expression1, expression2, expression3,…)
• alternative to CONCAT():
  • use || between columns
  • syntax: {col1} || ‘ ‘ || {col2} AS {new_col}

CONCAT_WS(' ', prefix, first, last, suffix) AS title FROM names;

Extract Functions

EXISTS ()

• link: https://www.w3schools.com/sql/sql_exists.asp
• syntax: SELECT * FROM {table} WHERE EXISTS (SELECT {col} FROM {table} WHERE {condition});
• Used to test for existense of any record in subquery
  • SELECT 1 FROM table can also be used instead of actually selecting columns in EXISTS table
• Similar to joining and getting the column from another table. Exists simply returns True for conditions that match, and the outer SELECT statement gets the information from the EXISTS table

-- syntax for exists
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

FILTER ()

• Simple filter function to aggregate functions on row
  • COUNT(1) FILTER (WHERE staff_id=’1′) AS mike_count
  • SUM(amount) FILTER (WHERE staff_id=’2′) AS jon_amount

Views

-- Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition

SELECT * from view_name;

SQL Shortcuts

See All Columns From One Table When Joining

• Syntax: SELECT {table}.* FROM {table} JOIN {table2} ON {table.key}={table2.foreign_key}

Fun Code

-- using WITH to group 2 columns at different levels
create view members_approved_for_voucher (id, name, email, total_spending) as
  with target_departments (id)
  as
  (
      select d.id
      from departments as d
          inner join sales as s
              on s.department_id = d.id
          inner join products as p
              on p.id = s.product_id
      group by d.id
      having SUM(p.price) > 10000
  )
  select m.id, m.name, m.email, SUM(p.price)
  from members as m
      inner join sales as s
          on s.member_id = m.id
      inner join target_departments as td
          on td.id = s.department_id
      inner join products as p
          on p.id = s.product_id
  group by m.id, m.name, m.email
  having SUM(p.price) > 1000;

select id, name, email, total_spending
from members_approved_for_voucher
order by id;

-- Different Way To Join
CREATE VIEW members_approved_for_voucher AS
  SELECT M.id, M.name, M.email, SUM(P.price) AS total_spending
  FROM members AS M, sales AS S, products AS P, departments as D
  WHERE M.id = S.member_id AND P.id = S.product_id AND D.id = S.department_id
  AND 
  (SELECT SUM(products.price) 
   FROM products, sales
   WHERE products.id=sales.product_id AND D.id=sales.department_id) > 10000
  GROUP BY M.id
  HAVING total_spending > 1000;
  
SELECT *
FROM members_approved_for_voucher