SQL Interview Questions : Customer Orders , Sql Server

Language: Layout:

/*
Question 1: You are given following two tables,
Customer{cust_id, cust_name, ...<Other customer related details>}
Order{order_id, order_name, cust_id, ...<Other order related details>}

You have to provide the output in following format.
cust_id, cust_name, [Total amount of orders]

Please note that you have to do this in SQL/Scope, and print only those customer who have at least one order.

create table Customer(cust_id int, cust_name varchar(255),gender varchar(5));
create table Orders(order_id int,order_name varchar(255),cust_id int,order_timestamp date,order_price int);

insert into Customer values(1,'Pallavi','F');
insert into Customer values(2,'Shinchan','M');
insert into Customer values(3,'El','M');
insert into Customer values(4,'Monika','F');

insert into Orders values(1,'Laptop',1,'20210201',100);
insert into Orders values(2,'Headphones',1,'20210205',50);
insert into Orders values(3,'TV',2,'20200201',300);
insert into Orders values(4,'Cleaner',3,'20200101',400);
insert into Orders values(5,'PS',3,'20201221',600);

select * from Customer;
select * from Orders;

select a.cust_name,a.cust_id,count(distinct b.order_id)
from
(
    select cust_name,cust_id
    from Customer
)a
join
(
    select order_id,cust_id from orders
)b
on a.cust_id = b.cust_id
group by a.cust_id,a.cust_name
having count(distinct b.order_id)>=1

/*
Question 2: Find out all customers who haven't placed any order in the past six months.
*/
select b.cust_name
from
(
    select * from Orders
    where order_timestamp < DATEADD(m,-6,GetDate())
)a
join
(
    select * from Customer
)b
on a.cust_id = b.cust_id
;

/*
Question 3: write a SQL query to find out customer's total purchasing payment in the past six months.
*/

select b.cust_name,sum(a.order_price) as 'total purchase amount'
from
(
    select * from Orders
    where order_timestamp >= DATEADD(m,-6, GetDate())
)a
join
(
    select * from Customer
)b
on a.cust_id = b.cust_id
group by a.cust_id,b.cust_name

View schema
Execution time: 0,06 sec, rows selected: 16, rows affected: 9, absolute service time: 0,25 sec, absolute service time: 0,26 sec