assm3

with recursive date_range(day_value) as (
select min(date(orderdate)) from orders
union all
select day_value+interval 1 day from date_range
where day_value<(select max(date(orderdate)) from orders)
)
select day_value from date_range
where day_value not in (select date(orderdate) from orders);

select categoryName,customers.country,
count(orderID),sum((1-Discount)*Quantity*orderdetails.unitprice) as revenue
from orders left outer join orderdetails using(orderID) 
left outer join products using(productID)
left outer join categories using(categoryID)
left outer join customers using(customerID)
group by categoryName,country with rollup;  

select customerID,companyName,count(orderID) as OrderReceived,
rank() over (order by count(orderID) desc) as C_rank
from customers left outer join orders using(customerID)
group by customerID;      

with t1 as (
 select row_number() over (order by date(orderdate)) as rows_number,
date(orderdate) as date,
sum((1-Discount)*Quantity*orderdetails.unitprice) as revenue
 from orders left outer join orderdetails using (orderID)
 group by date(orderdate))
 select date,
 case when rows_number>6 then avg(revenue) over (rows between 6 preceding and current row)
 else null
 end sales_moving_avergae_7
 from t1;       

with win1 as(select distinct date(orderdate) as date,employees.EmployeeID
from orders cross join employees),
win2 as(SELECT employeeID,date(orderdate) as date,
sum((1-Discount)*Quantity*orderdetails.unitprice) as rev
from employees left outer join orders using (employeeID)
left outer join orderdetails using(orderID)
group by employeeID,DATE),
win3 as(SELECT date,employeeid,IFNULL(win2.REV,0) AS REV FROM win1 left outer join win2 using(employeeID,date))
SELECT*,rank() over(partition by date order by rev desc) as e_rank FROM win3;

with t1 as(select customerID,orderID,sum((1-Discount)*Quantity*orderdetails.unitprice) as revenue
from orders left outer join orderdetails using(orderID)
group by customerID,orderID)
select*,revenue-lag(revenue) over(partition by customerID order by orderID) as diffprev,
revenue-lead(revenue) over(partition by customerID order by orderID) as diffnext
from t1;   

with 7_temp as(
with temp as(
 select customerid,orderid,sum((unitprice*(1-discount)*quantity)) as revenue
 from orders left join orderdetails using (orderid)
 group by orderid)
 select temp.customerid,temp.orderid, revenue,
 -(revenue-lead(revenue) over (partition by temp.customerid order by orderdate))  as diffnext,
 rank() over (partition by customerid order by date(orderdate)) as o_rank
 from temp left join orders using(orderid))
 select 
 (select count(customerid) from
  (select distinct customerid from orders group by customerid having count((orderid)) >1) as c)/
 (select count(distinct customerid)from orders) 
    as repeat_purchases,
    avg(diffnext/revenue) as sales_growth_rate
from 7_temp
where o_rank=1;