SQL Challenges-1: Average Selling Price

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

From the following tables write a SQL query to find the average selling price for each item. Return item code and average_selling_price. average_selling_price should be rounded to 2 decimal places.

Input:

Table: item_price

Structure:

Field	Type	Null
item_code	int(11)	YES
date_from	date	YES
date_to	date	YES
item_cost	int(11)	YES

Data:

item_code	date_from	date_to	item_cost
101	2018-04-07	2018-06-28	8
102	2018-02-15	2018-04-17	13
103	2018-03-12	2018-04-30	10
101	2018-06-29	2018-10-31	15
103	2018-05-01	2019-08-24	14
102	2018-04-18	2018-07-10	25
104	2018-06-11	2018-10-10	25
101	2018-11-01	2019-01-15	20

Table: sale

Structure:

Field	Type	Null
sale_date	date	YES
item_code	int(11)	YES
sale_qty	int(11)	YES

Data:

sale_date	item_code	sale_qty
2018-05-15	101	120
2018-04-27	103	80
2018-04-10	102	200
2018-07-12	101	100
2018-07-07	103	50
2018-09-17	104	100
2018-06-25	102	100

Sample Solution:

SQL Code(MySQL):

Create table  item_price (item_code integer, date_from date, date_to date, item_cost integer);
Create table  sale (sale_date date,item_code integer,sale_qty integer);


insert into item_price  values (101, '2018-04-07', '2018-06-28', 8);
insert into item_price  values (102, '2018-02-15', '2018-04-17', 13);
insert into item_price  values (103, '2018-03-12', '2018-04-30', 10);
insert into item_price  values (101, '2018-06-29', '2018-10-31', 15);
insert into item_price  values (103, '2018-05-01', '2019-08-24', 14);
insert into item_price  values (102, '2018-04-18', '2018-07-10', 25);
insert into item_price  values (104, '2018-06-11', '2018-10-10', 25);
insert into item_price  values (101, '2018-11-01', '2019-01-15', 20);



insert into sale  values ('2018-05-15',101,120);
insert into sale  values ('2018-04-27',103, 80);
insert into sale  values ('2018-04-10',102, 200);
insert into sale  values ('2018-07-12',101, 100);
insert into sale  values ('2018-07-07',103, 50);
insert into sale  values ('2018-09-17',104, 100);
insert into sale  values ('2018-06-25',102, 100);



SELECT sl.item_code, 
ROUND(SUM(sl.sale_qty*ip.item_cost)/SUM(sl.sale_qty),2) AS average_selling_price
FROM sale sl
LEFT JOIN item_price ip 
ON sl.item_code = ip.item_code 
AND sl.sale_date BETWEEN ip.date_from AND ip.date_to
GROUP BY item_code;

Sample Output:

item_code|average_selling_price|
---------|---------------------|
      101|                11.18|
      102|                17.00|
      103|                11.54|
      104|                25.00|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Order status report for each month for each company to find booked and cancelled order number and quantity.
Next: All People Report to the Given Manager.