Finding when requests are met or exceeded by customer by month

Question

I have a table that has customers and I want to find what month the customer met or exceeded a certain number of requests.

The table has customer_id a timestamp of each request.

What I am looking for is the month (or day) that the customer met or exceeded 10000 requests. I've tried to get a running total in place but this just isn't working for me. I've left it in the code in case someone knows how I can do this.

What I have is the following:

SELECT 
       customer_id
       , DATE_TRUNC(CAST(TIMESTAMP_MILLIS(created_timestamp) AS DATE), MONTH) as cMonth
       , COUNT(created_timestamp) as searchCount
--     , SUM(COUNT (DISTINCT(created_timestamp))) OVER (ROWS UNBOUNDED PRECEDING) as RunningTotal2
FROM customer_requests.history.all
GROUP BY distributor_id, cMonth
ORDER BY 2 ASC, 1 DESC;

The representation I am after is something like this.

customer    requests    cMonth       totalRequests
cust1       6000         2017-10-01  6000
cust1       4001         2017-11-01  10001
cust2       4000         2017-10-01  4000
cust2       4000         2017-11-01  8000
cust2       4000         2017-12-01  12000
cust2       3000         2017-12-01  3000
cust2       3000         2017-12-01  6000
cust2       3000         2017-12-01  9000
cust2       3000         2017-12-01  12000

Answer 1

Assuming SQL Server, try this (adjusting the cutoff at the top to get the number of transactions you need; right now it looks for the thousandth transaction per customer).

Note that this will not return customers who have not exceeded your cutoff, and assumes that each transaction has a unique date (or is issued a sequential ID number to break ties if there can be ties on date).

DECLARE @cutoff INT = 1000;
WITH CTE
AS (SELECT customer_id,
           transaction_ID,
           transaction_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date, transaction_ID) AS RN,
           COUNT(transaction_ID) OVER (PARTITION BY customer_id) AS TotalTransactions
    FROM #test)
SELECT DISTINCT
       customer_id,
       transaction_date as CutoffTransactionDate,
       TotalTransactions
FROM CTE
WHERE RN = @cutoff;

How it works:

row_number assigns a unique sequential identifier to each of a customer's transactions, in the order in which they were made. count tells you the total number of transactions a person made (assuming again one record per transaction - otherwise you would need to calculate this separately, since distinct won't work with the partition).

Then the second select returns the 1,000th (or however many you specify) row for each customer and its date, along with the total for that customer.

Answer 2

this is my solution.

SELECT
  customerid
 ,SUM(requests) sumDay
 ,created_timestamp
FROM yourTable
GROUP BY 
  customerid,
  created_timestamp
HAVING SUM(requests) >= 10000;

Its pretty simple. You just group according to your needs, sum up the requests and select the rows that meet your HAVING clause. You can try the query here.

Answer 3

If you want a cumulative sum, you can use window functions. In Standard SQL, this looks like:

SELECT customer_id, 
       DATE_TRUNC(CAST(TIMESTAMP_MILLIS(created_timestamp) AS DATE), MONTH) as cMonth
       COUNT(*) as searchCount,
       SUM(COUNT(*)) OVER (ORDER BY MIN(created_timestamp) as runningtotal
FROM customer_requests.history.all
GROUP BY distributor_id, cMonth
ORDER BY 2 ASC, 1 DESC;