1 Upvote

SQL creating a view

SQL
Querying

https://www.codewars.com/kata/5811527d9d278b242f000006/train/sql

CREATE VIEW members_approved_for_voucher AS
SELECT 
  m.id as id
  , m.name as name
  , m.email as email
  , SUM(p.price) total_spending
FROM sales s
  JOIN products p ON s.product_id=p.id
  JOIN members m ON s.member_id=m.id
WHERE s.department_id IN
  (SELECT s.department_id
  FROM sales s
    JOIN products p ON s.product_id=p.id
  GROUP BY s.department_id
  HAVING SUM(p.price)>10000)
GROUP BY 1,2,3
HAVING SUM(p.price)>1000
ORDER BY m.id;

SELECT * from members_approved_for_voucher;
CREATE VIEW members_approved_for_voucher AS
SELECT 
  m.id
  , m.name
  , m.email
  , SUM(p.price) total_spending
FROM 
  sales s
  , members m
  , products p
WHERE 
  s.member_id=m.id
  AND s.product_id=p.id
  AND s.department_id IN (
    SELECT s.department_id
    FROM sales s JOIN products p ON s.product_id=p.id
    GROUP BY department_id
    HAVING SUM(p.price)>10000
  )
GROUP BY 1,2,3
HAVING SUM(p.price)>1000
ORDER BY 1;
  
SELECT * FROM members_approved_for_voucher;

By changjulian17 - Last Updated Dec. 23, 2021, 1:44 p.m.

Did you find this snippet useful?

Sign up to bookmark this in your snippet library

COMMENTS
RELATED SNIPPETS
left join LATERAL
SQL
Querying

2
median
SQL
Querying

2
SQL window function
SQL
Querying

1
SQL RANK
SQL
Querying

1
EXISTS
SQL
Querying

1
Top Contributors
75