CODEWITHSUNDEEP
sqlgoogle-bigquery
Alen Giliana
Alen Giliana

Reputation: 2154

SQL query to find count of continuous years from current date

I have a data table of donation_dates by donor_id. I would like to return the number of continuous years of donations.

WITH DONATIONS AS 
(
    SELECT 123 AS donor_account_id, DATE '2022-10-10' AS donation_date        
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2021-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2020-09-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2019-10-10' AS donation_date    
    UNION ALL
    SELECT 123 AS donor_account_id, DATE '2017-10-10' AS donation_date    
    UNION ALL  -- shouldn't count
    SELECT 456 AS donor_account_id, DATE '2021-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2020-01-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2019-10-10' AS donation_date    
    UNION ALL
    SELECT 456 AS donor_account_id, DATE '2019-01-10' AS donation_date    
    UNION ALL
    SELECT 789 AS donor_account_id, DATE '2020-10-10' AS donation_date    
    UNION ALL -- shouldn't count
    SELECT 789 AS donor_account_id, DATE '2019-10-10' AS donation_date                
    -- shouldn't count
)

I was able to get sequential years if entry exists for current year (as in id 123). However, since current year is not done, I should also include donors from last year since they could still donate by end of year (as in id 456).

Here is my code:

SELECT 
    CY.ID AS id, COUNT(CY.YEAR) AS cont_years
FROM 
    (SELECT 
         DY.ID, DY.YEAR, 
         RANK() OVER (PARTITION BY DY.ID ORDER BY DY.YEAR DESC) AS CT,  
         (YEAR + RANK() OVER (PARTITION BY DY.ID ORDER BY DY.YEAR DESC)) AS FLAG
     FROM 
         (SELECT DISTINCT 
              D.donor_account_id ID, 
              EXTRACT(YEAR FROM D.donation_date) YEAR
          FROM 
              DONATIONS D
          GROUP BY 
              1, 2) DY
     ORDER BY 
         1, 2 DESC) CY
  WHERE 
      CY.FLAG = EXTRACT(YEAR FROM CURRENT_DATE) + 1
  GROUP BY 
      1

These are the expected correct results from the table above, but my code is unable to query id 456:

enter image description here

Upvotes: 0

Views: 98

Answers (1)

Jaytiger
Jaytiger

Reputation: 12264

You might consider below query.

SELECT DISTINCT donor_account_id, COUNT(DISTINCT donation_year) OVER w1 AS cont_years FROM (
  SELECT *, donation_year - DENSE_RANK() OVER w0 AS part
    FROM (SELECT *, EXTRACT(YEAR FROM donation_date) donation_year FROM DONATIONS)
  WINDOW w0 AS (PARTITION BY donor_account_id ORDER BY donation_year)
) QUALIFY COUNTIF(donation_year IN (2021, 2022)) OVER w1 > 0
WINDOW w1 AS (PARTITION BY donor_account_id, part);

+------------------+------------+
| donor_account_id | cont_years |
+------------------+------------+
|              123 |          4 |
|              456 |          3 |
+------------------+------------+

Upvotes: 1

Related Questions