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?