Attempting to create a table with the total quantity sold by product and select the third highest quantity sold product segmented by date. Keep getting error
Invalid Column name
for the alias for my RANK () OVER statement:
select
RANK () OVER (PARTITION BY t3.orderdate order by t3.total_amt_ordered) as ranking,
t3.productid,
t3.orderdate,
t3.total_amt_ordered
from
(select
t2.productid,
t1.orderdate,
SUM(t2.orderqty) as total_amt_ordered
from
saleslt.salesorderheader t1
inner join
saleslt.salesorderdetail t2 on t1.salesorderid = t2.salesorderid
group by
productid, orderdate) t3
where
ranking = 3;
The below query will return all the product ids partitioned at order date level and all the order quantities which ranked at three.
SELECT * FROM
(
select
DENSE_RANK () OVER (PARTITION BY t3.orderdate order by
t3.total_amt_ordered DESC ) as ranking,
t3.productid,
t3.orderdate,
t3.total_amt_ordered
from (
select
t2.productid,
t1.orderdate,
SUM(t2.orderqty) as total_amt_ordered
from
saleslt.salesorderheader t1
inner join
saleslt.salesorderdetail t2
on t1.salesorderid=t2.salesorderid
group by productid, orderdate) t3
) Z
where Z.ranking= 3;