BigQuery: Rolling daily count visitor's summary of payment

Question

I have this data:

date	visitor_id	total_payment
2022-01-01	A	20
2022-01-01	B	15
2022-01-01	C	20
2022-01-02	B	10
2022-01-02	D	25

I'd like to have daily count of visitor with total_payment equal or greater than 20$, with that being said, result I'm hoping is:

date	count_visitor
2022-01-01	2
2022-01-02	4

2022-01-01 is 2 because only A and C have payment more than 20$, however on 2022-01-02 additional 2 more because B is 35$ (sum) and D is 25$.

Is there any possible query for this? I hope I'm clear on my description. Thank you in advance.

Answer 1

Welcome @Indri

The query below will give you a running sum of the rows per day where the total_amount of greater than of equal to 20, I believe this should give you the answer you are looking for:

WITH data AS(
  SELECT "2022-01-01" AS date, "A" AS visitor_id, 20 AS total_payment
  UNION ALL
  SELECT "2022-01-01" AS date, "B" AS visitor_id, 15 AS total_payment
  UNION ALL
  SELECT "2022-01-01" AS date, "C" AS visitor_id, 20 AS total_payment
  UNION ALL
  SELECT "2022-01-02" AS date, "A" AS visitor_id, 10 AS total_payment
  UNION ALL
  SELECT "2022-01-02" AS date, "D" AS visitor_id, 25 AS total_payment
)

SELECT
*,
COUNT(*) OVER(ORDER BY date)
FROM data
WHERE total_payment >= 20

Answer 2

You can use IF EXISTS but you can't reference column values in inserted without set-based access to inserted:

IF EXISTS (SELECT 1 FROM inserted WHERE [user_id] = '9999999' AND [enabled] = 0)
BEGIN
    UPDATE dpa_web_user SET enabled = 1 WHERE user_id = '9999999';
END

You may want to add AND enabled <> 1 to prevent updating a row for no reason.

You can do this in a single statement though:

UPDATE u
  SET enabled = 1
  FROM dbo.dpa_web_user AS u
  INNER JOIN inserted AS i
  ON u.[user_id] = i.[user_id]
  WHERE u.[user_id] = '9999999'
    AND i.[user_id] = '9999999'
    AND u.enabled <> 1
    AND i.enabled = 0;