SQL Challenges-1: Find cost percent for each order of total order amount

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

From the following tables write a query in SQL to find the cost percent for each order of total order amount of a customer. Return customer ID, customer name, order details, order amount and cost percent.

Table: orders

Structure:

Data:

Table: customer

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

create table customer(
cust_code		varchar(10) not null unique,	
cust_name		varchar(25),
cust_city		varchar(25),
working_area	varchar(25),
cust_country	varchar(25),
grade			integer,
opening_amt		decimal(10,2),
receive_amt		decimal(10,2),
payment_amt		decimal(10,2),
outstanding_amt  decimal(10,2),
phone_no 		varchar(15)
);



insert into customer values('C00013','Holmes     ','London    ','London   ','UK       ', 2, 6000.00, 5000.00,7000.00, 4000.00,'BBBBBBB     ');
insert into customer values('C00001','Micheal    ','New York  ','New York ','USA      ', 2, 3000.00, 5000.00,2000.00, 6000.00,'CCCCCCC     ');
insert into customer values('C00020','Albert     ','New York  ','New York ','USA      ', 3, 5000.00, 7000.00,6000.00, 6000.00,'BBBBSBB     ');
insert into customer values('C00025','Ravindran  ','Bangalore ','Bangalore','India    ', 2, 5000.00, 7000.00,4000.00, 8000.00,'AVAVAVA     ');
insert into customer values('C00024','Cook       ','London    ','London   ','UK       ', 2, 4000.00, 9000.00,7000.00, 6000.00,'FSDDSDF     ');
insert into customer values('C00015','Stuart     ','London    ','London   ','UK       ', 1, 6000.00, 8000.00,3000.00,11000.00,'GFSGERS     ');
insert into customer values('C00002','Bolt       ','New York  ','New York ','USA      ', 3, 5000.00, 7000.00,9000.00, 3000.00,'DDNRDRH     ');
insert into customer values('C00018','Fleming    ','Brisban   ','Brisban  ','Australia', 2, 7000.00, 7000.00,9000.00, 5000.00,'NHBGVFC     ');
insert into customer values('C00021','Jacks      ','Brisban   ','Brisban  ','Australia', 1, 7000.00, 7000.00,7000.00, 7000.00,'WERTGDF     ');
insert into customer values('C00019','Yearannaidu','Chennai   ','Chennai  ','India    ', 1, 8000.00, 7000.00,7000.00, 8000.00,'ZZZZBFV     ');
insert into customer values('C00005','Sasikant   ','Mumbai    ','Mumbai   ','India    ', 1, 7000.00,11000.00,7000.00,11000.00,'147-25896312');
insert into customer values('C00007','Ramanathan ','Chennai   ','Chennai  ','India    ', 1, 7000.00,11000.00,9000.00, 9000.00,'GHRDWSD     ');
insert into customer values('C00022','Avinash    ','Mumbai    ','Mumbai   ','India    ', 2, 7000.00,11000.00,9000.00, 9000.00,'113-12345678');
insert into customer values('C00004','Winston    ','Brisban   ','Brisban  ','Australia', 1, 5000.00, 8000.00,7000.00, 6000.00,'AAAAAAA     ');
insert into customer values('C00023','Karl       ','London    ','London   ','UK       ', 0, 4000.00, 6000.00,7000.00, 3000.00,'AAAABAA     ');
insert into customer values('C00006','Shilton    ','Torento   ','Torento  ','Canada   ', 1,10000.00, 7000.00,6000.00,11000.00,'DDDDDDD     ');
insert into customer values('C00010','Charles    ','Hampshair ','Hampshair','UK       ', 3, 6000.00, 4000.00,5000.00, 5000.00,'MMMMMMM     ');
insert into customer values('C00017','Srinivas   ','Bangalore ','Bangalore','India    ', 2, 8000.00, 4000.00,3000.00, 9000.00,'AAAAAAB     ');
insert into customer values('C00012','Steven     ','San Jose  ','San Jose ','USA      ', 1, 5000.00, 7000.00,9000.00, 3000.00,'KRFYGJK     ');
insert into customer values('C00008','Karolina   ','Torento   ','Torento  ','Canada   ', 1, 7000.00, 7000.00,9000.00, 5000.00,'HJKORED     ');
insert into customer values('C00003','Martin     ','Torento   ','Torento  ','Canada   ', 2, 8000.00, 7000.00,7000.00, 8000.00,'MJYURFD     ');
insert into customer values('C00009','Ramesh     ','Mumbai    ','Mumbai   ','India    ', 3, 8000.00, 7000.00,3000.00,12000.00,'Phone No    ');
insert into customer values('C00014','Rangarappa ','Bangalore ','Bangalore','India    ', 2, 8000.00,11000.00,7000.00,12000.00,'AAAATGF     ');
insert into customer values('C00016','Venkatpati ','Bangalore ','Bangalore','India    ', 2, 8000.00,11000.00,7000.00,12000.00,'JRTVFDD     ');
insert into customer values('C00011','Sundariya  ','Chennai   ','Chennai  ','India    ', 3, 7000.00,11000.00,7000.00,11000.00,'PPHGRTS     ');

	

create table orders(
ord_num		integer(10) not null unique,	
ord_amount		integer,
advance_amount		integer,
ord_date	date,
cust_code	varchar(10),
ord_description		varchar(25),
foreign key(cust_code) references customer(cust_code)
);


 insert into orders values(200122, 2500, 400,'2008-09-16','C00007','mother board');
 insert into orders values(200118,  500, 100,'2008-07-20','C00022','mouse');
 insert into orders values(200119, 4000, 700,'2008-09-16','C00007','RAM');
 insert into orders values(200121, 1500, 600,'2008-09-23','C00008','key board');
 insert into orders values(200130, 2500, 400,'2008-07-30','C00025','mother board');
 insert into orders values(200108, 4000, 600,'2008-02-15','C00008','mouse');
 insert into orders values(200105, 2500, 500,'2008-07-18','C00025','RAM');
 insert into orders values(200109, 3500, 800,'2008-07-30','C00015','key board');
 insert into orders values(200125, 2000, 600,'2008-10-10','C00008','hard disk');
 insert into orders values(200123,  500, 100,'2008-09-16','C00022','mother board');
 insert into orders values(200116,  500, 100,'2008-07-13','C00010','mouse');
 insert into orders values(200126,  500, 100,'2008-06-24','C00022','hard disk');
 insert into orders values(200129, 2500, 500,'2008-07-20','C00022','RAM');
 insert into orders values(200127, 2500, 400,'2008-07-20','C00015','mouse');
 insert into orders values(200128, 3500,1500,'2008-07-20','C00009','mother board');
 insert into orders values(200131,  900, 150,'2008-08-26','C00012','hard disk');
 insert into orders values(200133, 1200, 400,'2008-06-29','C00009','mouse');
 insert into orders values(200100, 1000, 600,'2008-01-08','C00015','RAM');
 insert into orders values(200107, 4500, 900,'2008-08-30','C00007','hard disk');
 insert into orders values(200113, 4000, 600,'2008-06-10','C00022','key board');
 insert into orders values(200102, 2000, 300,'2008-05-25','C00012','mother board');




select
    c.cust_code,
    c.cust_name,
    o.ord_description,
    o.ord_amount,
    o.ord_amount / sum(o.ord_amount) 
	OVER (PARTITION BY c.cust_name) AS "Percentage of Cost"
FROM
    orders o
JOIN
    customer c
ON o.cust_code = c.cust_code

Sample Output:

cust_code|cust_name  |ord_description|ord_amount|Percentage of Cost|
---------+-----------+---------------+----------+------------------+
C00022   |Avinash    |key board      |      4000|            0.5000|
C00022   |Avinash    |mouse          |       500|            0.0625|
C00022   |Avinash    |mother board   |       500|            0.0625|
C00022   |Avinash    |hard disk      |       500|            0.0625|
C00022   |Avinash    |RAM            |      2500|            0.3125|
C00010   |Charles    |mouse          |       500|            1.0000|
C00008   |Karolina   |mouse          |      4000|            0.5333|
C00008   |Karolina   |key board      |      1500|            0.2000|
C00008   |Karolina   |hard disk      |      2000|            0.2667|
C00007   |Ramanathan |hard disk      |      4500|            0.4091|
C00007   |Ramanathan |RAM            |      4000|            0.3636|
C00007   |Ramanathan |mother board   |      2500|            0.2273|
C00009   |Ramesh     |mother board   |      3500|            0.7447|
C00009   |Ramesh     |mouse          |      1200|            0.2553|
C00025   |Ravindran  |RAM            |      2500|            0.5000|
C00025   |Ravindran  |mother board   |      2500|            0.5000|
C00012   |Steven     |mother board   |      2000|            0.6897|
C00012   |Steven     |hard disk      |       900|            0.3103|
C00015   |Stuart     |RAM            |      1000|            0.1429|
C00015   |Stuart     |key board      |      3500|            0.5000|
C00015   |Stuart     |mouse          |      2500|            0.3571|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Employees who are earning more salary than their managers.
Next: Resolve highest complaint and nearest manager id is 114.