[SQL]
SQLの問題
条件付き集計
以下のようなテレアポの営業成績テーブルがあるとする
* 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');
- 問
- テーブルの構造とデータを利用して、以下の条件を満たすカラム1~5を出力するクエリを作成せよ
# | 内容 |
---|---|
column1 | agent_idを出力する |
column2 | callの数の合計値:これはagentごとのレコードの合計になる |
column3 | 種別(=type)が'sales'で結果(=result)が'yes'のもののレコードの合計 |
column4 | 種別(=type)が'sales'で結果(=result)が'yes'のもののvalueの合計 |
column5 | コンバージョン率、(column3/column2) * 100 |
- 答
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;