Write a query that provides a weekly report, summarizing per merchant, total number of orders, count of orders where the order total is under $100, approval rate (%) by order count, and approval rate (%) by order amount, leveraging orders.total_order_price.

Note - to calculate approval rate: (approved orders) / (all orders)

Expected output columns: merchant_name, total_orders_count, orders_under100_count, approval_rate_by_count, approval_rate_by_sum

Below is the data to query from:

CREATE TABLE orders ( order_id INT PRIMARY KEY, created_at TIMESTAMP, merchant_name VARCHAR(255), payment_method VARCHAR(255), total_spent NUMERIC, order_source VARCHAR(255), browser_ip VARCHAR(255), email VARCHAR(255), credit_card_bin VARCHAR(255), customer_id VARCHAR(255) );

CREATE TABLE products ( order_id INT, product_title VARCHAR(255), product_id VARCHAR(255), quantity NUMERIC, unit_price NUMERIC, FOREIGN KEY (order_id) REFERENCES orders(order_id) );

CREATE TABLE logs ( order_id INT, log_created_at TIMESTAMP, raw_log VARCHAR(255), FOREIGN KEY (order_id) REFERENCES orders(order_id) );

INSERT INTO orders (order_id, created_at, merchant_name, payment_method, total_spent, order_source, browser_ip, email, credit_card_bin, customer_id) VALUES (123, "2021-07-05 02:45:21", "merchant_111", "credit_card", 2398.67, "", "184.166.176.14", "don_draper@randommail.com", "427961", "1458690481"), (456, "2021-07-05 13:10:10", "merchant_111", "credit_card", 803.1, "desktop_web", "99.100.136.239", "michael.scott@randommail.com", "453352", "4052154883"), (789, "2021-07-06 04:17:13", "merchant_125", "credit_card", 231.24, "", "69.46.227.226", "oliviapope123@randommail.com", "376748", "1512350021"), (567, "2021-07-06 05:40:45", "merchant_125", "paypal", 56.93, "mobile_web", "96.232.92.72", "q8b86ca8uq@privaterelay.appleid.com", "", "5253484495"), (345, "2021-07-06 11:20:23", "merchant_125", "credit_card", 67.08, "", "69.46.227.226", "walter_white@randommail.com", "410064", "5200453016"), (987, "2021-07-06 17:24:45", "merchant_125", "giftcard", 798.16, "", "173.93.32.231", "sheldon.cooper345@randommail.com", "", "5116924222"), (902, "2021-07-01 13:17:34", "merchant_129", "paypal", 2412.21, "", "69.46.227.226", "larrydavid890@randommail.com", "", "4052154883"), (678, "2021-07-01 10:34:24", "merchant_129", "paypal", 202.34, "mobile_app", "96.240.128.181", "k2re2qdhct@privaterelay.appleid.com", "", "4319279403"), (907, "2021-07-07 05:46:39", "merchant_392", "credit_card", 164.53, "desktop_web", "98.148.216.218", "leslie_knope@randommail.com", "473522", "1512350021"), (478, "2021-07-07 05:37:27", "merchant_392", "credit_card", 59.76, "", "69.46.227.226", "tina.belcher@randommail.com", "552302", "5253484495"), (637, "2021-07-08 06:23:11", "merchant_392", "credit_card", 738.82, "mobile_app", "74.73.225.210", "5ws425xdfg@privaterelay.appleid.com", "474437", "1512350021");

INSERT INTO products (order_id, product_title, product_id, quantity, unit_price) VALUES (123, "Queen Split Wood Box Spring", "4435206223", 2, 330.7), (123, "Standard Classic Bean Bag", "4825116219", 1, 67.08), (123, "Buena Park Sleigh Bed", "4825124229", 1, 2000.89), (456, "Plastic Adirondack Chair", "4907287661", 3, 803.1), (789, "Cement Pot Planter", "4691786229", 1, 78.61), (789, "Fortuna Pub Table", "4829868402", 1, 152.63), (567, "7 Piece Abner Picture Frame Set", "5001413696", 1, 56.93), (345, "Standard Classic Bean Bag", "4404169351", 1, 67.08), (987, "Flight Flat Crib Diaper Stacker", "4412875021", 2, 59.34), (987, "Newtown Desk", "3718692343", 1, 738.82), (902, "Emrich Outdoor Reclining Chaise Lounge", "4827012931", 2, 411.32), (902, "Buena Park Sleigh Bed", "4826754112", 1, 2000.89), (678, "Kinsella Coffee Table with Storage", "4876324581", 1, 202.34), (907, "Jared Hand-Tufted Wool Grey Area Rug", "4534590661", 1, 164.53), (478, "Boisvert Printed Damask Sheer Grommet Curtain Panel", "4681320789", 2, 59.76), (637, "Newtown Desk", "4693039385", 1, 738.82); INSERT INTO logs (order_id, log_created_at, raw_log) VALUES (123, '2021-07-05 02:46:10','order approved'), (456, '2021-07-05 13:07:36','order declined'), (456, '2021-07-05 13:08:11','email updated'), (456, '2021-07-05 13:10:10','order declined'), (567, '2021-07-06 05:30:10','order approved'), (567, '2021-07-06 05:39:10','shipping city updated'), (567, '2021-07-06 05:40:45','order approved'), (345, '2021-07-06 11:21:23','order declined'), (987, '2021-07-06 17:21:10','order declined'), (987, '2021-07-06 17:23:00','email updated'), (987, '2021-07-06 17:24:45','order approved'), (902, '2021-07-01 13:17:34','order declined'), (478, '2021-07-07 05:38:30','order approved'), (907, '2021-07-07 05:47:56','order approved'), (637, '2021-07-08 06:24:10','order declined');