Create a column with daily count in impala

Question

I want to create a count column which will has the count per day. I have managed to do it like this:

select book, orders, s.common_id,s.order_date,d.customer_region,t.cnt
from books_tbt as s
inner join  customer_tbt as d
on s.common_id = d.common_id
inner join (select count(*) as cnt,order_date from customer_tbt where customer !='null' 
group by order_date) as t
on t.order_date = d.order_date
where d.customer !='null'
and s.order_date = 20220122
group by book, orders, s.common_id,s.order_date,d.customer_region,t.cnt;

I want to ask if there is a more efficient way to do it?

Answer 1

You can simply use COUNT(*) OVER( Partitioned by ORDER_DATE Order by ORDER_DATE) window function to calculate count for an order date.

select book, orders, s.common_id,s.order_date,d.customer_region,d.cnt
from books_tbt as s
inner join  
( select d.*, COUNT(*) OVER( Partition by ORDER_DATE Order by ORDER_DATE) as cnt from customer_tbt d) as d on s.common_id = d.common_id -- count(*) over can not be calculated together with group by so we are using a sub qry
where d.customer !='null'
and s.order_date = 20220122
group by book, orders, s.common_id,s.order_date,d.customer_region,d.cnt;