CODEWITHSUNDEEP
sqlgoogle-bigquerybq
clucko87
clucko87

Reputation: 89

SUM for a week's worth of data

Currently, I've got a query at work that works like this:

SELECT  ColumnA, ColumnB, PurchaseAmount/100 FROM TableX 
JOIN TableY ON TableX.A = TableY.B
WHERE PurchaseAmount/100 > 299
and PurchaseTimestamp >= DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 DAY)

So this query runs once a day. Very simple, works great. My question is, how could I say, SUM the PurchaseAmount (so separation purchases) to $1000 for a week? Is that even possible?

So a sample result from original query would be:

Col A (User ID PurchaseAmount
1 $600
1 $800
2 $700
3 $1100

And I would desire to return: 1 and 3, since these are the "IDs" that have a SUM over $1000. (Complicating this is, of course, that these Purchases are happening over the course of a week; not one single day.)

Upvotes: 0

Views: 180

Answers (1)

shawnt00
shawnt00

Reputation: 17925

with data as (
    SELECT ColumnA, ColumnB, PurchaseAmount / 100 as Amt,
        sum(PurchaseAmount / 100) over (partition by ColumnA) as CustomerTotal
    FROM TableX INNER JOIN TableY ON TableX.A = TableY.B
    WHERE PurchaseAmount / 100 > 299
      AND PurchaseTimestamp >= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)
)
select ColumnA, ColumnB, Amt
from data where CustomerTotal > 1000;

I am guessing that you want to expand the selection to a full seven days rather than just the single day of the posted query and then return all customer purchases in that range. Here's one way to augment your query with a grand total per customer that can be used for filtering. The purpose of division by 100 is unclear but you should be able to easily adjust that according to your data. Similarly with the comparison against 299.

You might also want to just use a subquery to compare against a list of customers that match the weekly test via a separate query.

SELECT ColumnA, ColumnB, PurchaseAmount / 100 as Amt,
FROM TableX INNER JOIN TableY ON TableX.A = TableY.B
WHERE PurchaseAmount / 100 > 299
  AND PurchaseTimestamp >= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 DAY)
  AND ColumnA IN (
    SELECT ColumnA
    FROM TableX INNER JOIN TableY ON TableX.A = TableY.B
    WHERE PurchaseAmount / 100 > 299
      AND PurchaseTimestamp >= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)
    GROUP BY ColumnA
    HAVING SUM(PurchaseAmount / 100) > 1000
);

To get the total with the results you might try:

with p as (
    SELECT ColumnA, ColumnB, PurchaseAmount / 100 as Amt,
        sum(PurchaseAmount / 100) over (partition by ColumnA) as CustomerTotal
    FROM TableX INNER JOIN TableY ON TableX.A = TableY.B
    WHERE PurchaseAmount / 100 > 299
      AND PurchaseTimestamp >= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)
)
select * from p
where PurchaseTimestamp >= DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 DAY)
    and CustomerTotal > 1000;

This does assume that the final result is a subset of the larger result used to compute the weekly threshold. That seems to be true in your case.

Upvotes: 1

Related Questions