SQL Queries

1) how many companies do not have any parent companies?
select count(*)
from accounts
where subsidiary_of is null

2) How many companies have parent companies?
select count(*)
from accounts
where subsidiary_of is not null

3)Which is the oldest company?
select *
from accounts
where year_established in (select min(year_established) from accounts)

4)Which company makes the largest revenue?
select *
from accounts
where revenue in (select max(revenue) from accounts)

5)Which country has the maximum number of offices?
select distinct office_location, count(*) as no_of_office
from accounts
group by office_location
limit 1

6)which company has maximum employees
select *
from accounts
where employees in (select max(employees) from accounts)

7)Do any products have better win rates?
select product, concat(round(sum(case when deal_stage='Won' then 1 else 0 end)*100/count(*),1),'%') as win_rate
from sales_pipeline
group by 1
order by 2 desc

#Products and revenue
select product,sum(close_value) as total_revenue,dense_rank() over(order by sum(close_value) desc) as rnk
from sales_pipeline
group by 1

8) Which product has the most lost deals?
select product, concat(round(sum(case when deal_stage='Lost' then 1 else 0 end)*100/count(*),1),'%') as Lost_rate
from sales_pipeline
group by 1
order by 2 desc
limit 1

9)Which product has the highest revenue?
Select product,close_value,dense_rank() over(order by close_value desc) rnk
from sales_pipeline
group by 1
10)Can you identify any quarter-over-quarter trends?
with cte as(
select product,close_value, case when month(close_date) in(1,2,3) then 'Q1'
								 when month(close_date) in(4,5,6) then 'Q2'
                                 when month(close_date) in(7,8,9) then 'Q3'
                                 when month(close_date) in(10,11,12) then 'Q4'
                                 end as Quarter_sales
from sales_pipeline
where close_value is not null
group by 1,2),
cte2 as(
select quarter_sales,sum(close_value) as current_quarter,lag(sum(close_value),1) over() as previous_quarter
from cte
group by 1),
cte3 as(
select *,round((current_quarter-previous_quarter)*100/previous_quarter,1) as QoQ_growth
from cte2)
select *
from cte3

11)Which manager has made the maximum revenue
select st.manager,sum(close_value) as total_revenue,dense_rank() over(order by sum(close_value) desc) as rnk
from sales_pipeline sp join sales_teams st on sp.sales_agent=st.sales_agent
where sp.deal_stage='won'
group by 1

12)How is each sales team performing compared to the rest? 
select st.regional_office,count(*) total_transactions
from sales_pipeline sp join sales_teams st on sp.sales_agent=st.sales_agent
where sp.deal_stage='won'
group by st.regional_office
order by 2 desc

13)Are any sales agents lagging behind?
select sp.sales_agent,count(*) total_transactions_lost
from sales_pipeline sp 
where sp.deal_stage='lost'
group by 1
order by 2 desc

14)which sales agents are doing good?
select sp.sales_agent,count(*) total_transactions_won
from sales_pipeline sp 
where sp.deal_stage='won'
group by 1
order by 2 desc

15)Which sales agent has closed the deal in the least number of days
select distinct sales_agent,timestampdiff(day,engage_date,close_date) as sucess_close_deal_time
from sales_pipeline
where close_date is not null and deal_stage='Won'
order by 2 

16) Avg engaging days
select round(avg(timestampdiff(day,engage_date,close_date)),0) as close_deal_time
from sales_pipeline
16)Top 5 sectors based on revenue generated
select sector, round(sum(revenue),0) total_revenue,dense_rank() over(order by sum(revenue) desc) rnk
from accounts
group by 1