SQL Inner Query Alias And Join

 use sample_first_db;


select * from sample_first_db..customers;


select * from sample_first_db..[orders];


-- april may month customers order count


--step 1

select *, month([order date]) mnth from orders where month([order date]) in (4,5)


--step2

select [customer id], mnth, count(distinct [order id]) cnt from

(select *, month([order date]) mnth from orders where month([order date]) in (4,5)) a

group by [customer id],mnth


--step3


select [customer id],MONTH([order date]) as month, COUNT(DISTINCT [order id]) count from orders 

where MONTH([order date]) in (4,5) group by [customer id],MONTH([order date])


select [customer id],

case

when months = 4 then 'April' 

When months = 5 then 'May'

end as month_name,

counts from

(select [customer id],

MONTH([order date]) as months, 

COUNT(DISTINCT [order id]) counts 

from orders 

where MONTH([order date]) in (4,5) 

group by [customer id],MONTH([order date]))inner_table;



-- inner table alias name use. use join



select inner_table2.[customer id], [customer name], [age], inner_table2.month_name,inner_table2.counts

from

(select [customer id],

case

when months = 4 then 'April' 

When months = 5 then 'May'

end as month_name,

counts from

(select [customer id],

MONTH([order date]) as months, 

COUNT(DISTINCT [order id]) counts 

from orders 

where MONTH([order date]) in (4,5) 

group by [customer id],MONTH([order date]))inner_table1)inner_table2

join customers c 

on inner_table2.[customer id] = c.[customer id];


------------------------------------select all


select * from

(select [customer id] as cid, 

case  

when month = 4 then 'April' 

when month = 5 then 'May' 

end as month, count(distinct [order id]) oidcount from

(select *, month([order date]) as month from orders where month([order date]) in(4,5))a

group by [customer id],month)b

inner join customers c

on b.cid = c.[customer id]

;


-------------exchange table in join and select all


select * from customers c inner join 

(select * from

(select [customer id] as cid, 

case  

when month = 4 then 'April' 

when month = 5 then 'May' 

end as month, count(distinct [order id]) oidcount from

(select *, month([order date]) as month from orders where month([order date]) in(4,5))a

group by [customer id],month )b)d 

on d.cid = c.[customer id]

;



-------------SECOND QUESTION

---customer bought in may month and from jharkhand


--only may

select *, [customer name] cname from

(select * from orders where MONTH([order date]) in (5) )a

inner join customers c 

on a.[customer id] = c.[customer id];


-- with all details


select *, [customer name] cname from

(select * from orders where MONTH([order date]) in (5) )a

inner join customers c 

on a.[customer id] = c.[customer id] where state = 'Jharkhand';


----only name

select distinct [customer name] cname from

(select * from orders where MONTH([order date]) in (5) )a

inner join customers c 

on a.[customer id] = c.[customer id] where state = 'Jharkhand';


---second method


select distinct cname from

(select cid , [customer name] cname from

(select [customer id] cid, MONTH([order date]) month from orders where MONTH([order date]) in (5) )a

inner join customers c 

on a.cid = c.[customer id] where c.state = 'Jharkhand')d; 



-------------Third Question 

--------- write a query to get customer name and their latest order information


----step1 

select a.*,b.*, rank() over(order by a.[order date] desc) rank 

from orders a inner join customers b 

on a.[customer id] = b.[customer id];


--step2

select [customer name], [order id], [order date],  [amount],rank from

(select b.[customer name], a.[order id], [order date],[amount], 

rank() over(partition by b.[customer id] order by a.[order date] desc) rank     --important

from orders a inner join customers b 

on a.[customer id] = b.[customer id])c where c.rank = 1;


----point to be noted if a.[customer id] = b.[customer id] these two column name are same then in that

--- select statement don't use select *, rank() over ...... use specific column name like this one

---select b.[customer name], a.[order id], rank() over(partition by

----other wise there will be an error if use a.[customer id] = b.[customer id])c

--The column 'customer id' was specified multiple times for 'c'.

---try this for error 

-- select [customer name], [order id] from

--(select *, rank() over(partition by b.[customer id] order by a.[order date] desc) rank

--from orders a inner join customers b 

--on a.[customer id] = b.[customer id])c where c.rank = 1;


--4th question wite query to get top 2 customer id and name based on total transaction value for each month


select * from 

(select *,rank() over (partition by mnth order by total_amount desc) as rank from 

(select o.[customer id] cid, c.[customer name] cname, month(o.[order date]) mnth, sum(amount) total_amount

from orders o inner join customers c 

on o.[customer id] = c.[customer id] group by o.[customer id], c.[customer name], month(o.[order date])) a)b

where rank<3;

o.[customer id] cid, c.[customer name] cname, month(o.[order date]) mnth


Comments

Popular Posts

15 Cases where True Indology exposed Devdutt Pattanaik

Maharishi Sushruta : Father of Indian Medicine, Father of Plastic Surgery- A Great Rishi and Scholar

Mythologist Author Devdutt Pattanaik's Abusive Tweets