Counting Cumulatives for unique customers

Question

So I have this table:

bid_date     bid_count     cum_quote_count     unique_cust     cum_unique_cust
--------     ---------     ---------------     -----------     ---------------

3/31/2015       25             25                 5                  5
4/01/2015       50             75                 12                 10
4/02/2015       5              80                 5                  13
4/03/2015       48             128                25                 20
4/04/2015       61             189                9                  32

What I'm tracking here is the cum_unique_cust column. This counts the cumulative unique customers for that day and the days before, however, I do it manually. For example, if I wanted to get the cumulative unique customers for 4/01/2015, I would write DATE(created_at) BETWEEN '2015-03-31' AND '2015-04-01', and then do it again for the next dates, copying the results on to Excel.

Is there any way this could be done in MySQL automatically, without me copying and pasting them in Excel?

It looks like you don't really need cum_ columns as you can use SUM(unique_cust) and SUM(bid_count) aggregates to get the right values — dmitryro, Jul 01 '16 at 00:25
I don't understand the whole 'paste into excel' bit. Perhaps you're after the max date older than the target date — Strawberry, Jul 01 '16 at 00:38

score 0 · Answer 1 · edited May 23 '17 at 10:28

You can use try something like this

SELECT
  current_cum.*,
  COALESCE(current_cum.Value - prev_cum.Value, 0) AS cum_unique_cust
FROM
  your_table    AS current_cum
LEFT JOIN
  your_table    AS prev_cum
    ON prev_cum.bid_date = (SELECT MAX(bid_date)
                          FROM your_table
                         WHERE bid_date < current_cum.bid_date)

MySQL - Subtracting value from previous row, group by

score 0 · Answer 2 · edited May 23 '17 at 11:55

Use date_sub to get previous date.

or your can use date_add to get next date of given date.

SELECT `unique_cust`,sum(`cum_unique_cust`) 
FROM `test_table` 
where `bid_date` between 2015-03-31 and date_add('2015-03-31',interval 1 day ) 
group by `unique_cust` 
INTO OUTFILE 'textfile.csv' 
FIELDS TERMINATED BY '|'

Here is more information for extracting data into excel automatically.

Exporting results of a Mysql query to excel?

how to save mysql query output to excel or .txt file?

score 0 · Answer 3 · answered Jul 01 '16 at 02:03

You're missing a lot of relevant details so I'm reading between the lines. First I'm assuming that you want to do this as an update. Two, I believe you have to query a separate table to get a count of the customers.

You can use a join if you want but that's not standard SQL. I get the impression that MySQL isn't always so great when it comes to handling subqueries. I'd still recommend giving this a shot first.

update <table>
set cum_unique_cust = (
    select count(distinct customerId)
    from <customers>
    where created_at '2015-03-31' and <table>.bid_date
)

Counting Cumulatives for unique customers

3 Answers3