CODEWITHSUNDEEP
sqlgoogle-bigquery
Nabih Bawazir
Nabih Bawazir

Reputation: 7255

How to do query on multiple dates on certain range on Google Big Query

I'm doing quite long query to find a customer with certain condition on certain dates, in this case '2019-6-20', the query is like this

Here's my code

select current_date() as date , count(customer_id) as cell13
from(
select customer_id, count(id) as total, string_agg(payment_state order by created_at desc limit 1) as cek
from(
select distinct(A.id), A.customer_id, extract(month from A.created_at) as months,extract(day from A.created_at) as days, extract(year from A.created_at) as years, payment_state, A.created_at, A.grandtotal_cents
from bl.orders as A
left join bl.blacklists as B
on A.customer_id = B.customer_id
where date(A.created_at) >= date_sub(date('2019-6-20') , interval 60 day) and grandtotal_cents > 0 and B.customer_id is null
)
group by customer_id
having cek = "unpaid")

Here's the result

Row  date          cell13
1    2019-06-21    696

Now I need to query this to multiple dates in certain date range, for example 2019-03-23 to 2019-06-21. How suppose I do this, so the output will like

Row  date          cell13
1    2019-06-21    696
...
90   2019-03-23    ...

Upvotes: 0

Views: 705

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can generate a table of dates using generate_date_array() and unnest() and then use this with a left join.

Overall, though, your query is a message an hard to follow, but here is the idea:

with dates as (
      select dte
      from (select generate_date_array('2019-03-23', '2016-06-21', interval 1 day) d
           ) d cross join
           unnest(d.d) dte
     )
select . . .
from dates left join
     bl.orders o
     on date(o.created_at) >= date_sub(dte, interval 60 day)
     . . .

Upvotes: 1

Related Questions