Codewars SQL. 5 Kyu | By Isabelle | JEN-LI CHEN IN DATA SCIENCE

Home » Codewars 5 Kyu Solutions » Codewars SQL. 5 Kyu | By Isabelle | JEN-LI CHEN IN DATA SCIENCE

Maybe your like

SitemapOpen in app

Sign up

Sign in

Medium LogoWriteSearch

Sign up

Sign in

JEN-LI CHEN IN DATA SCIENCE

JEN-LI CHEN IN DATA SCIENCE

My homepage to record my thought processes for solving SQL and Algorithm questions

IsabelleIsabelle2 min readNov 22, 2020

--

Listen

Share

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 ASSELECT m.id, m.name, m.email, SUM(p.price) as total_spendingFROM members m, sales s, products pWHERE m.id = s.member_idAND s.product_id = p.idAND 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.idHAVING SUM(p.price) > 1000ORDER BY m.id;SELECT * from members_approved_for_voucher;

Other Solution:

CREATE VIEW members_approved_for_voucher ASSELECT m.id, m.name, m.email, SUM(p.price) AS total_spendingFROM members mINNER JOIN sales s ON s.member_id = m.idINNER JOIN products p ON p.id = s.product_idWHERE 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.emailHAVING SUM(p.price) > 1000ORDER 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

CodewarsSqlSolutionsKataJEN-LI CHEN IN DATA SCIENCEJEN-LI CHEN IN DATA SCIENCE

Published in JEN-LI CHEN IN DATA SCIENCE

93 followersLast published Jun 21, 2021

My homepage to record my thought processes for solving SQL and Algorithm questions

IsabelleIsabelle

Written by Isabelle

382 followers23 following

In love with telling stories with data

No responses yet

Help

Status

About

Careers

Press

Blog

Privacy

Rules

Terms

Text to speech

Tag » Codewars 5 Kyu Solutions