SQLの問題

[SQL]

SQLの問題

  条件付き集計

• In MySql, how do I group based on one column and do sums on others? より

以下のようなテレアポの営業成績テーブルがあるとする

* agent_id(=営業の従業員ID)
* type(=種別)
* result(=結果)
* value(=通話時間)
* date(=日にち)

 +----------+--------+-----------+-------+------------+
 |                      SALES                        |
 +----------+--------+-----------+-------+------------+
 | agent_id |  type  |  result   | value |    date    |
 +----------+--------+-----------+-------+------------+
 |        1 | sales  | yes       | 12.34 | 2018-05-01 |
 |        1 | sales  | yes       |  5.45 | 2018-05-01 |
 |        1 | return | other     |  7.00 | 2018-05-01 |
 |        1 | return | saved     | 19.99 | 2018-05-01 |
 |        1 | return | not_saved |  1.19 | 2018-05-01 |
 |        2 | return | saved     |  1.34 | 2018-05-01 |
 |        2 | return | not_saved | 29.04 | 2018-05-01 |
 |        2 | sales  | yes       | 11.11 | 2018-05-01 |
 |        3 | sales  | no        | 10.00 | 2018-05-01 |
 |        3 | sales  | no        | 89.34 | 2018-05-01 |
 |        3 | sales  | yes       |  1.41 | 2018-05-01 |
 |        3 | sales  | other     |  2.41 | 2018-05-01 |
 |        3 | sales  | other     |  2.41 | 2018-04-28 |
 +----------+--------+-----------+-------+------------+

• SQL Fiddleはこちら http://sqlfiddle.com

DROP TABLE IF EXISTS `SALES`;

CREATE TABLE IF NOT EXISTS `SALES` (
  `agent_id` VARCHAR(32),
  `type` VARCHAR(32),
  `result` VARCHAR(32),
  `value` DOUBLE,
  `comm_ts` DATE
);

INSERT INTO `SALES` 
VALUES 
(1, 'sales' , 'yes'      , 12.34, '2018-05-01'),
(1, 'sales' , 'no'       ,  5.45, '2018-05-01'),
(1, 'return', 'other'    ,  7.00, '2018-05-01'),
(1, 'return', 'saved'    , 19.99, '2018-05-01'),
(1, 'return', 'not_saved',  1.19, '2018-05-01'),
(2, 'return', 'saved'    ,  1.34, '2018-05-01'),
(2, 'return', 'not_saved', 29.04, '2018-05-01'),
(2, 'sales' , 'yes'      , 11.11, '2018-05-01'),
(3, 'sales' , 'no'       , 10.00, '2018-05-01'),
(3, 'sales' , 'no'       , 89.34, '2018-05-01'),
(3, 'sales' , 'yes'      ,  1.41, '2018-05-01'),
(3, 'sales' , 'other'    ,  2.41, '2018-05-01'),
(3, 'sales' , 'other'    ,  2.41, '2018-04-28');

問

テーブルの構造とデータを利用して、以下の条件を満たすカラム１～５を出力するクエリを作成せよ

答

SELECT agent_id, count(*) AS num_calls,
       sum(case when type = 'sales' and result = 'yes' then 1 else 0 end) as column3,
       sum(case when type = 'sales' and result = 'yes' then value else 0 end) as column4,
       avg(case when type = 'sales' and result = 'yes' then 1.0 else 0 end) as column5
FROM sales 
WHERE comm_ts > '2018-04-29' 
GROUP BY agent_id;