Testdome - Regional Sales Comparison

참고링크 : https://app.testdome.com/tests?sort=popularity+desc&generator=12 

https://www.youtube.com/watch?v=pkUzSs9fO0c 

 

C>

CREATE TABLE regions(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE states(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  regionId INTEGER NOT NULL,
  FOREIGN KEY (regionId) REFERENCES regions(id)
); 

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL, 
  stateId INTEGER NOT NULL,
  FOREIGN KEY (stateId) REFERENCES states(id)
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  amount INTEGER NOT NULL,
  employeeId INTEGER NOT NULL,
  FOREIGN KEY (employeeId) REFERENCES employees(id)
);

INSERT INTO regions(id, name) VALUES(1, 'North');
INSERT INTO regions(id, name) VALUES(2, 'South');
INSERT INTO regions(id, name) VALUES(3, 'East');
INSERT INTO regions(id, name) VALUES(4, 'West');
INSERT INTO regions(id, name) VALUES(5, 'Midwest');

INSERT INTO states(id, name, regionId) VALUES(1, 'Minnesota', 1);
INSERT INTO states(id, name, regionId) VALUES(2, 'Texas', 2);
INSERT INTO states(id, name, regionId) VALUES(3, 'California', 3);
INSERT INTO states(id, name, regionId) VALUES(4, 'Columbia', 4);
INSERT INTO states(id, name, regionId) VALUES(5, 'Indiana', 5);

INSERT INTO employees(id, name, stateId) VALUES(1, 'Jaden', 1);
INSERT INTO employees(id, name, stateId) VALUES(2, 'Abby', 1);
INSERT INTO employees(id, name, stateId) VALUES(3, 'Amaya', 2);
INSERT INTO employees(id, name, stateId) VALUES(4, 'Robert', 3);
INSERT INTO employees(id, name, stateId) VALUES(5, 'Tom', 4);
INSERT INTO employees(id, name, stateId) VALUES(6, 'William', 5);

INSERT INTO sales(id, amount, employeeId) VALUES(1, 2000, 1);
INSERT INTO sales(id, amount, employeeId) VALUES(2, 3000, 2);
INSERT INTO sales(id, amount, employeeId) VALUES(3, 4000, 3);
INSERT INTO sales(id, amount, employeeId) VALUES(4, 1200, 4);
INSERT INTO sales(id, amount, employeeId) VALUES(5, 2400, 5);

-- e.g. 'Minnesota' is the only state under the 'North' region. 
-- Total sales made by employees 'Jaden' and 'Abby' for the state of 'Minnesota' is 5000 (2000 + 3000)
-- Total employees in the state of 'Minnesota' is 2
-- Average sales per employee for the 'North' region = Total sales made for the region (5000) / Total number of employees (2) = 2500
-- Difference between the average sales of the region with the highest average sales ('South'), 
-- and the average sales per employee for the region ('North') = 4000 - 2500 = 1500.
-- Similarly, no sale has been made for the only state 'Indiana' under the region 'Midwest'.
-- So the average sales per employee for the region is 0.
-- And, the difference between the average sales of the region with the highest average sales ('South'), 
-- and the average sales per employee for the region ('Midwest') = 4000 - 0 = 4000.

-- Expected output (rows in any order):
-- name     average   difference
-- -----------------------------
-- North	2500	  1500             
-- South 	4000	  0
-- East		1200   	  2800
-- West		2400	  1600
-- Midwest  	0         4000

 

Q> 지역 별 매출 평균과 최대 평균 대비 매출 차이 

 

 

H> 

H> 가장 큰 테이블을 만들기 

 

 

A>

 

select    r.name                                                       

  ,                               sum(sa.amount) as SalesAmount

  ,                               count(employeeid) as emp

  ,                               ifnull(sum(sa.amount) / count(employeeid),0) as avg_amt

           from regions as r

                      left join states as st

                                 on r.id = st.regionid

                      left join employees as e

                                 on st.id = e.stateid

                      left join sales as sa

                                 on e.id = sa.employeeid

     group by 1

 )

  , t2 as (

 select max(avg_amt) as first_avg

   from t1

 )

 select name

 ,                     ifnull(avg_amt,0) as average

 ,                     first_avg - ifnull(avg_amt,0)  as difference

 from t1, t2

 group by 1

 order by 1 desc

 

 

 

L>

- 테이블을 온전히 그려가며 이해하기 

- ifnull 에 대한 리마인드 

- Max 값에 대한 새로운 접근 

- 한가지 문제에 대한 다양한 방법 시도