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:

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?

  • I don't think the counter idea works, unless I've misunderstood. If I am a customer on Monday and Friday, and then on Saturday you run the report for the last 7 days, how do you know from looking at the counters to only count me once? – Thomas Wormald Feb 16 '19 at 16:22
  • Good point, I missed that. Do you know what date ranges should be supported beforehand, or should it support arbitrary ranges? – JacquesB Feb 16 '19 at 16:46
  • No, the date ranges are flexible, up to 6 months ago. – Thomas Wormald Feb 16 '19 at 16:59
  • OK, you could every day (as a scheduled job) calculate for all day-ranges from 1 day back to 6 months backs. That is only 200 entries per shop per day, so not a lot of extra data if you have enough data that this is problem in the first place. – JacquesB Feb 16 '19 at 17:01

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.

| improve this answer | |

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.

| improve this answer | |
  • Thanks, I am working on a large database - several million transactions per day. Currently we are doing the calculation on the main Transaction table as you suggest, but it's taking several seconds to calculate, which is not acceptable. Eager Read Derivation is kind of what I was thinking about, although the requirement for unique customers over any date range (up to 6 months in the past) seems to kill this idea, as there is too much to pre-compute. – Thomas Wormald Feb 16 '19 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.