Return number of rows since last occurrence

I want to find the number of reports a user has submitted since a user last received a personalized response. The column response_type has 3 possible values: “No Response,” “Templated Response,” and “Personalized Response”

I wrote a query that has the most recent cases at the top.

SELECT 
    case_number, short_description, flight_date_only, 
    CAST(created_on AS DATE),
    origin_station, destination_station, flight_number, state_label,
    response_type, close_notes
FROM
    "foundry_sync"."Monthly Reports/voice_cases (w Response Type)-master-0309" 
WHERE 
    contact LIKE '%{{w_ContactName.text}}'
ORDER BY 
    CAST(created_on AS DATE) DESC

I would like to count the number of reports since the last response_type = “Personalized Response” and return the date of the last “Personalized Response”enter image description here

Answer

I want to find the number of reports a user has submitted since a user last received a personalized response.

You can use ROW_NUMBER() to assign a rank to each record by descending date within contact groups, and then check the row number of the last record having the concerned response, like:

SELECT 
    contact,
    MAX(CASE WHEN response_type = 'Personalized Response' THEN rn END) - 1 records_since_last_response
FROM (
    SELECT
        contact,
        response_type,
        ROW_NUMBER() OVER(PARTITION BY contact ORDER BY cast(created_on as date) DESC) rn
    FROM "foundry_sync"."Monthly Reports/voice_cases (w Response Type)-master-0309" t
) x
GROUP BY contact

To filter on a given contact, you can simply add a WHERE clause to the inner (or outer) query. If you really need this LIKE condition on the contact:

SELECT 
    MAX(CASE WHEN response_type = 'Personalized Response' THEN rn END) - 1 records_since_last_response
FROM (
    SELECT
        response_type,
        ROW_NUMBER() OVER(ORDER BY cast(created_on as date) DESC) rn
    FROM "foundry_sync"."Monthly Reports/voice_cases (w Response Type)-master-0309" t
    WHERE  contact LIKE '%{{w_ContactName.text}}'
) x