Finding Active customers by month- SQL- Big Query

Alejandro S 0
Viewed 79

I want to find a way to get the count of customers with promotion active per month, with the conditions on the dates included on the case statement. I tried the following query but there's an overlap in some of the ranges and I am not able not get the same count if i do one case per month. My idea was to create a loop/recursive method that takes the start and end time, but I'm having issues with the creation.

    SELECT
   region,
  #June data
  CASE
    WHEN DATE (start_time) >= '2022-06-01' AND DATE(end_time) <= '2022-06-30' 
    OR ( DATE(start_time) < '2022-06-01'AND DATE(end_time) >= '2022-06-01' AND DATE(end_time)<='2022-06-30')
     OR (DATE(end_time)>'2022-06-30' AND DATE(start_time)>='2022-06-01' AND DATE(start_time)<='2022-06-30') 
    THEN '2022-06-01'
  #July data
  WHEN DATE (start_time) >= '2022-07-01'AND DATE(end_time) <= '2022-07-31'
  OR ( DATE(start_time) < '2022-07-01'AND DATE(end_time) >= '2022-07-01'AND DATE(end_time)<='2022-07-31')
  OR (DATE(end_time)>'2022-07-31'AND DATE(start_time)>='2022-07-01'AND DATE(start_time)<='2022-07-31') 
  THEN '2022-07-01'
  #August data
    WHEN DATE (start_time) >= '2022-08-01' AND DATE(end_time) <= '2022-08-31' 
    OR ( DATE(start_time) < '2022-08-01'AND DATE(end_time) >= '2022-08-01' AND DATE(end_time)<='2022-08-31') 
    OR (DATE(end_time)>'2022-08-31' AND DATE(start_time)>='2022-08-01' AND DATE(start_time)<='2022-08-31')
     THEN '2022-08-01'
  ELSE
  STRING(DATE(DATE_TRUNC(start_time,month)))
END
  AS month,
  COUNT(DISTINCT customer_id) AS active_customers,
FROM
customers_table 
GROUP BY region,month

Let me know if more clarity is needed please, it is one of my first questions. Thanks!

google-bigquery sql
1 Answers

I'm assuming you want each row in WHEN to be an 'OR'. So the first row needs to be also wrapped in parenthesis.

Also each statement may be shortened using DATE_TRUNC()

Here is the example for the first WHEN:

    WHEN 
     (DATE (start_time) >= '2022-06-01' AND DATE(end_time) <= '2022-06-30')
     OR ( DATE(start_time) < '2022-06-01'AND DATE_TRUNC(end_time,month) = '2022-06-01')
     OR (DATE(end_time)>'2022-06-30' AND DATE_TRUNC(start_time,month) ='2022-06-01') 
    THEN '2022-06-01'

Hope that helps!

Xavier HAMIDA0
Related