Codewars SQL

5 kyu

SQL Basics: Simple VIEW

For this challenge you need to create a VIEW. This VIEW is used by a sales store to give out vouches to members who have spent over $1000 in departments that have brought in more than $10000 total ordered by the members id. The VIEW must be called members_approved_for_voucher then you must create a SELECT query using the view.

Tables and relationship below:

resultant table schema

  • id
  • name
  • email
  • total_spending

NOTE: Your solution should use pure SQL. Ruby is used within the test cases to do the actual testing.

Solution:

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

Other Solution:

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

SELECT * FROM members_approved_for_voucher;

4 similar code variations are grouped with this one

Training on SQL Basics: Simple VIEW | Codewars

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store