SQL Challenges-1: Average Selling Price

Last update on December 20 2024 11:39:33 (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:

Data:

Table: sale

Structure:

Data:

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.