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

Post a Comment

Popular Posts

15 Cases where True Indology exposed Devdutt Pattanaik

-
Image
I am one out of such thousands of Twitter users who finds True Indology ( @tiinexile ) to be the best available source of knowledge regarding India’s History. I decided to compile those 15 cases where True Indology has exposed fraud claims of Devdutt Pattanaik on Twitter. Case 1 Devdutt Pattanaik’s Claim :    True Indology’s Response:  "A woman is pure. Nothing about her is impure. Without her, no man would exist"- Kokashastra "Where women are revered, there the Gods rejoice"- Manusmriti Hinduism is the only extant religion that worships feminine as divine. I challenge you to produce a source for the statement Case 2 Devdutt Pattanaik's : Article - A classical language by devdutt pattanaik. Below screenshot is from that article True Indology’s Response:  In this article, Devdutt Pattanaik says that Prakrit came earlier than Sanskrit. I would have laughed at his ignorance, his utter lack of basic linguistic knowledge, and
Read more

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

-
Image
Maharishi Shushruta ( Sanskrit: सुश्रुत ) is a legendry scholar of the Indian medical science and founding father of surgery. 2600 Yrs ago Maharishi Shushruta together with his colleagues had conducted a variety of complex surgeries such as caesareans, artificial limbs, cataract, urinary stones, fractures, and most specially the plastic surgery. Ayurveda has its foundation in a triology of books. one of which is sushruta samhita. other two are charak samhita and astanga hridaya. Historian believe that he lived in 6th - 7th century BC and Practiced his work on the banks of river ganga in modern day varanasi. A famous book (Shalya Tantra) has been described by the Shushruta about all the surgical procedures. Shalya Tantra was later named as the Sushruta Samhita (have details about the ancient medical science of India, well known as the Ayurveda). Maharishi Shushruta is the author of Sushruta Samhita in Sanskrit language and had described all the amazing surgical procedures with accuracy
Read more

Mythologist Author Devdutt Pattanaik's Abusive Tweets

-
Image
Who is Devdutt Pattanaik ?  Devdutt Pattanaik  is an Indian mythologist, [1]  speaker, illustrator, and author, [2]  known for his writing on sacred lore, legends, folklore, fables, and parables. [3]  His work focuses largely on the areas of myth, religion, mythology, and management. Pattanaik has incorporated the Mahabharata and the Ramayana into human resource management. [4]  He has written books on the relevance of sacred stories, symbols, and rituals in modern times; his more popular books include  Myth = Mithya: A Handbook of Hindu Mythology [5] ;   Jaya: An Illustrated Retelling of the Mahabharata [6] ;  and  Sita: An Illustrated Retelling of the Ramayana [7]  and My Gita. Pattanaik writes columns for  Mid-Day , [8]  Times of India, [9]   Swarajya , [10]  CN Traveller, [11]  Daily O [12]  and Scroll.in. [13]  He hosts a radio show/podcast for Radio Mirchi  [14]  called the Devdutt Patnaik Show. - Wikipedia In a world without social media, Pattanaik would most likely continue to
Read more