Customers(Customer_ID, Customer_Name)
Orders(OrderID, Customer_ID, Amount, Order_Date).
--Write Query was to find Names of the top 5 customers
by Amount with total amount more than 1000 USD for the last 30 days.
Order(Oid,OCity,OAmount,ODate,Cid,CName)
Give me TotalOrder Count for each City for each Month for the City only starting with C and Total Order Count is moer than 4.
hi
i have created sample data and the first query
drop Table #Customers
create table #Customers(Customer_ID int, Customer_Name varchar(100))
insert into #Customers select 1,'Annie'
insert into #Customers select 2,'Beth'
insert into #Customers select 3,'Kevin'
insert into #Customers select 4,'Tom'
insert into #Customers select 5,'Harry'
insert into #Customers select 6,'Axel'
insert into #Customers select 7,'Foley'
insert into #Customers select 8,'Tim'
insert into #Customers select 9,'Susan'
insert into #Customers select 10,'Rhonda'
select * from #Customers
drop Table #Orders
create table #Orders(OrderID int, Customer_ID int, Amount decimal(10,2), Order_Date Date)
insert into #Orders select 123,1,234.50,'2022-10-25'
insert into #Orders select 124,1,500.67,'2022-10-22'
insert into #Orders select 125,1,800.67,'2022-10-21'
insert into #Orders select 300,2,234.50,'2022-10-25'
insert into #Orders select 301,2,500.67,'2022-10-22'
insert into #Orders select 302,2,800.67,'2022-09-21'
insert into #Orders select 300,3,678.50,'2022-10-25'
insert into #Orders select 301,3,500.67,'2022-10-22'
insert into #Orders select 303,3,800.67,'2022-09-21'
insert into #Orders select 300,4,234.50,'2022-10-25'
insert into #Orders select 301,4,500.67,'2022-10-22'
insert into #Orders select 304,4,1000.67,'2022-09-21'
insert into #Orders select 300,5,234.50,'2022-10-25'
insert into #Orders select 301,5,500.67,'2022-10-22'
insert into #Orders select 305,5,800.67,'2022-09-21'
insert into #Orders select 300,6,234.50,'2022-10-25'
insert into #Orders select 301,6,500.67,'2022-10-22'
insert into #Orders select 306,6,800.67,'2022-10-21'
insert into #Orders select 300,7,234.50,'2022-10-25'
insert into #Orders select 301,7,500.67,'2022-10-22'
insert into #Orders select 307,7,800.67,'2022-09-21'
insert into #Orders select 300,8,678.50,'2022-10-25'
insert into #Orders select 301,8,500.67,'2022-10-22'
insert into #Orders select 308,8,800.67,'2022-09-21'
insert into #Orders select 300,9,234.50,'2022-10-25'
insert into #Orders select 301,9,500.67,'2022-10-22'
insert into #Orders select 309,9,800.67,'2022-09-21'
insert into #Orders select 300,10,234.50,'2022-10-25'
insert into #Orders select 301,10,890.67,'2022-10-22'
insert into #Orders select 3010,10,800.67,'2022-09-21'
select * from #Orders
select
top 5 Customer_Name,sum(amount)
from
( select
a.Customer_ID
, a.Customer_Name
, b.OrderID
, b.Amount
, b.Order_Date
from
#Customers a join #Orders b
on a.Customer_ID = b.Customer_ID
where
datediff(dd,order_date,getdate()) <= 30 ) a
group by
Customer_Name
having
sum(amount) > 1000
