SQL Challenges-1: Compare the purchasing status of the average purchase quantity of products of a category to the average pruchase quantity of the distributor

Last update on December 20 2024 11:39:38 (UTC/GMT +8 hours)

From the following tables write a query in SQL to compare the purchasing status of the average purchase quantity of products of a category to the average pruchase quantity of the distributor. Return purchase month, category_id and purchase status.

Input:

Table: product

Structure:

Data:

Table: purchase

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE  product(
product_id INTEGER(5) NOT NULL unique,
category_id   INTEGER(4) NOT NULL);

 
insert into product values(8001,150);
insert into product values(8002,160);
insert into product values(8003,160);
insert into product values(8004,150);
insert into product values(8005,160);	 

CREATE TABLE purchase (
purchase_no INTEGER(5) NOT NULL unique,
item_code   INTEGER(4) NOT NULL,
purchase_qty  integer(5),
purchase_date  date,
foreign key (item_code) references product(product_id));

insert into purchase values(1001,8001,240,'2019-12-17');
insert into purchase values(1002,8002,150,'2019-12-17');
insert into purchase values(1003,8003,175,'2020-11-15');
insert into purchase values(1004,8004,150,'2019-12-17');
insert into purchase values(1005,8005,145,'2019-12-05');
insert into purchase values(1006,8001,150,'2020-01-05');
insert into purchase values(1007,8002,200,'2020-01-15');
insert into purchase values(1008,8003,150,'2020-12-17');
insert into purchase values(1009,8001,200,'2020-01-28');
insert into purchase values(1010,8002,180,'2020-02-07');
insert into purchase values(1011,8001,300,'2020-02-25');
insert into purchase values(1012,8005,100,'2020-01-27');


SELECT pur.month purchase_month,category_id, 
CASE WHEN category_average > distributor_average then 'increase'
     WHEN category_average < distributor_average then 'decrease'
     WHEN category_average = distributor_average then 'remain same'
END AS purchase_status
FROM 
    (SELECT SUBSTR(purchase_date,1,7) month,category_id,avg(purchase_qty) category_average
    FROM purchase JOIN product ON purchase.item_code = product.product_id
    group by 1,2) pur JOIN 
    (SELECT SUBSTR(purchase_date,1,7) month,avg(purchase_qty) distributor_average
    FROM purchase group by 1) dis 
    ON pur.month = dis.month;

Sample Output:

purchase_month|category_id|purchase_status|
--------------+-----------+---------------+
2019-12       |        150|increase       |
2020-01       |        150|increase       |
2020-02       |        150|increase       |
2019-12       |        160|decrease       |
2020-01       |        160|decrease       |
2020-02       |        160|decrease       |
2020-11       |        160|remain same    |
2020-12       |        160|remain same    |

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Salesman who makes Largest Number of transactions.
Next: Highest difference in total sale of all quarters on a product of many companies.