SQLize Online / PHPize Online  /  SQLtest Online

create table del_test_credit_card (value_day varchar(25),card_id varchar(25), channel varchar(25)); INSERT ALL INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-02', 'AB111', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-02','AB112', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-03','AB113','Delivery') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-03','AB114','Delivery') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-03','AB115','Delivery') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-04', 'AB116', 'Branch') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-04', 'AB117', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-04', 'AB118', 'Agent') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-04', 'AB119', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-04', 'AB121', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-04', 'AB122', 'Delivery') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-05', 'AB123', 'Branch') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-05', 'AB124', 'Delivery') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-05', 'AB125', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-06', 'AB126', 'Delivery') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-06', 'AB127', 'Branch') INTO del_test_credit_card(value_day , card_id , channel ) values ('2019-06-08', 'AB128', 'Agent') INTO del_test_credit_card(value_day , card_id, channel ) values ('2019-06-08', 'AB129', 'Agent') SELECT * FROM DUAL; create table del_test_credit_card_limit (value_day varchar(25),card_id varchar(25), card_limit int); INSERT ALL INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-02', 'AB111', 10000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-02','AB112', 50000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-03','AB113', 100000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-03','AB114', 30000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-03','AB115', 10000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-04','AB115', 45000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-04', 'AB116', 50000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-04', 'AB117', 30000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-04', 'AB118', 10000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-04', 'AB118', 30000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-08', 'AB118', 20000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-04', 'AB119', 20000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-04', 'AB121', 50000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-04', 'AB122', 60000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-05', 'AB123', 100000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-05', 'AB124',10000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-05', 'AB124',45000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-05', 'AB125', 70000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-06', 'AB126', 95000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-06', 'AB127', 25000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-08', 'AB128', 20000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-09', 'AB128', 50000) INTO del_test_credit_card_limit(value_day , card_id , card_limit ) values ('2019-06-20', 'AB128', 30000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-08', 'AB129', 45000) INTO del_test_credit_card_limit(value_day , card_id, card_limit ) values ('2019-06-08', 'AB129', 90000) SELECT * FROM DUAL; create table del_test_credit_card_pos (value_day varchar(25),card_id varchar(25), turnover int); INSERT ALL INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB111', 1000) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30','AB112', 5000) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30','AB113', 50000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30','AB114', 20000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30','AB115', 38000) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB116', 0) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB117', 2500) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB118', 18000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB119', 10000) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB121', 45000) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB122', 40000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB123', 98000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB124',40000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB125', 0) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB126', 0) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB127', 25000) INTO del_test_credit_card_pos(value_day , card_id , turnover ) values ('2019-06-30', 'AB128', 25000) INTO del_test_credit_card_pos(value_day , card_id, turnover ) values ('2019-06-30', 'AB129', 55000) SELECT * FROM DUAL; WITH LatestLimits AS ( SELECT cl.card_id, MAX(cl.value_day) AS max_value_day FROM del_test_credit_card_limit cl GROUP BY cl.card_id ), FinalLimits AS ( SELECT cl.card_id, MAX(cl.card_limit) AS card_limit FROM del_test_credit_card_limit cl JOIN LatestLimits ll ON cl.card_id = ll.card_id AND cl.value_day = ll.max_value_day GROUP BY cl.card_id ) SELECT cc.channel, round(AVG(COALESCE(cp.turnover, 0) / NULLIF(fl.card_limit, 0) * 100),0) AS "Процент утилизации" FROM del_test_credit_card cc LEFT JOIN FinalLimits fl ON cc.card_id = fl.card_id LEFT JOIN del_test_credit_card_pos cp ON cc.card_id = cp.card_id GROUP BY cc.channel;