Most efficient way to get unique customer count

Question

I'm working on a system that stores details of customer purchases for several stores. One statistic that they would like is to know how many unique customers they have had over a specified day range, for a particular store, or all stores.

One way I can think to do this is storing data in a relational database (SQL), like so:

CREATE TABLE TransactionCustomers
(
    ShopId int,
    TransactionDay datetime2,
    CustomerId int
)

And then to query how many customers between two dates:

SELECT COUNT(DISTINCT(CustomerId))
FROM TransactionCustomers
WHERE TransactionDay BETWEEN '2019-02-01' AND '2019-02-14'
AND ShopId = 3

I'm wondering if anyone can think of a way to do this that shifts the processing workload onto the application that writes the transactions - basically pre-computing the unique customer count? Or is there a technology other than a relational database that is better suited for this calculation?

Answer 1

You can do some pre-calculation at transaction time by maintaining a separate table with unique (ShopId, TransactionDay, CustomerId) entries. The difference from TransactionCustomers is there is only one entry per customer per day per shop, so multiple transaction per day for a customer have been collapsed. This will allow you to have a primary key on all three, which will make the data-range query very fast.

If this is still not fast enough you might add an additional level of pre-calculation: Every day you run a scheduled job which generates the count for all day ranges from one day back to 6 months back. This will essentially pre-calculate counts for all date ranges, so ad-hoc queries will be instantaneous.

Answer 2

Unless you have a huge database (think Walmart proportions), a relational database should work fine, at least if you create the right indices (in this case, probably one on the combination of ShopId and TransactionDay). It might even work on the main Transaction table, though locks could be tricky. This is also by far the simplest solution.

If it doesn't work fast enough, consider applying Eager Read Derivation. Basically, whenever a transaction comes in, you're already precalculating the results of the reports. This will only work if the reports themselves are predefined (e.g. users will have the option 'last week', 'last two weeks', 'last month', but not a flexible date range, and only for a single store at the time). All of this can still be done in a relational database.