Submissions for Codewars Katas

Codewars-CASE.sql

/*
https://www.codewars.com/kata/593ef0e98b90525e090000b9
SQL Basics - Monsters using CASE
*/
SELECT top.*
  ,bot.*
  ,CASE
    WHEN top.heads > top.arms THEN 'BEAST'
    WHEN bot.tails > bot.legs THEN 'BEAST'
    ELSE 'WEIRDO'
END AS species
FROM top_half top
INNER JOIN bottom_half bot on top.id = bot.id
ORDER BY species;

Codewars-Fix-JOIN.sql

/*
https://www.codewars.com/kata/580fb94e12b34dd1c40001f0
SQL Bug Fixing: Fix the JOIN
*/
SELECT 
  j.job_title,
  ROUND((SUM(j.salary) / COUNT(p)),2)::float as average_salary,
  COUNT(p.id) as total_people,
  ROUND(SUM(j.salary),2)::float as total_salary
  FROM people p
    JOIN job j
    ON j.people_id = p.id
  GROUP BY j.job_title
  ORDER BY average_salary DESC; 

Codewars-Fix-Query.sql

/*
https://www.codewars.com/kata/582cba7d3be8ce3a8300007c
SQL Bug Fixing: Fix the QUERY - Totaling
*/
SELECT 
  s.transaction_date::date as day,
  d.name as department,
  COUNT(s.id) as sale_count
  FROM department d
    JOIN sale s on d.id = s.department_id
  group by day, d.name
  order by day asc

Codewars-Format-String.py

#https://www.codewars.com/kata/53368a47e38700bd8300030d
#Format a string of names like 'Bart, Lisa & Maggie'.
def namelist(names):
    string_Name = ''
    if(len(names) == 1):
        return names[0]["name"]
    elif(len(names) == 2):
        string_Name = f'{names[0]["name"]} & {names[1]["name"]}'
    elif(len(names) > 2):
        for i in range(0, len(names)-1):
            string_Name = f'{string_Name}{names[i]["name"]}, '
        string_Name = f'{string_Name[:-2]} & {names[len(names)-1]["name"]}'
    return string_Name

Codewars-PIVOT-Without-Crosstab.sql

/*
https://www.codewars.com/kata/5982020284a83baf2f00001c
SQL Basics: Simple PIVOTING data WITHOUT CROSSTAB
*/
SELECT 
  products.name,
  SUM(CASE WHEN details.detail = 'good' THEN 1 ELSE 0 END) AS good,
  SUM(CASE WHEN details.detail = 'ok' THEN 1 ELSE 0 END) AS ok,
  SUM(CASE WHEN details.detail = 'bad' THEN 1 ELSE 0 END) AS bad
FROM products
LEFT JOIN details ON details.product_id = products.id
GROUP BY products.name
ORDER BY name

Codewars-Printer-Errors.py

#https://www.codewars.com/kata/56541980fa08ab47a0000040
#Printer Errors
def printer_error(s):
    letters_good = "abcdefghijklm"
    letters_good_search = list(letters_good)
    s_split = list(s)
    letters_error = [letter
                     for letter in s_split
                     if letter not in letters_good_search
                    ]
    letters_error_count = len(letters_error)    
    return f'{letters_error_count}/{len(s)}'

Codewars-VIEW.sql

/*
https://www.codewars.com/kata/5811527d9d278b242f000006
SQL Basics: Simple VIEW
*/
CREATE VIEW members_approved_for_voucher AS

WITH dept_spend AS
(
  SELECT 
  sal.department_id
  , SUM(prd.price)
  FROM sales sal
  INNER JOIN products prd
  ON sal.product_id = prd.id
  GROUP by sal.department_id
  HAVING SUM(prd.price) > 10000
)

SELECT 
  sal.member_id AS id
  , mem.name AS name
  , mem.email AS email
  , SUM(prd.price) AS total_spending
FROM sales sal
INNER JOIN departments dep
ON sal.department_id = dep.id
INNER JOIN products prd
ON sal.product_id = prd.id 
INNER JOIN members mem
ON sal.member_id = mem.id
WHERE 
  1 = 1
  AND sal.department_id IN (SELECT department_id FROM dept_spend)
GROUP BY
  sal.member_id
  , mem.name
  , mem.email
HAVING SUM(prd.price) > 1000
ORDER BY sal.member_id ASC
;

SELECT * from members_approved_for_voucher;

Codewars-WITH.sql

/*
https://www.codewars.com/kata/5811501c2d35672d4f000146
SQL Basics: Simple WITH
*/
WITH special_sales as
    (SELECT *
     FROM sales
     WHERE 1=1
     AND price > 90
    )
    SELECT *
    FROM departments dep
    WHERE dep.id IN 
    (SELECT department_id
     FROM special_sales
    );