I'm trying to build a report in BigQuery to pull the number of days user has been in negative balance. For example if user A has negative balance since 7/1/2022, I would like the report to show user A's account has been negative for 5 days (giving that today is 7/6/2022).

Any help is appreciated. Thanks!!

CodePudding user response：

The script below returns the number of days each user's account has been negative. It uses the DATEDIFF function to subtract today's date with the most recent date that the user's account had a positive or $0 balance.

CREATE TABLE UserBalance
(
    Date Date,
    UserName VARCHAR(100),
    Balance MONEY
)
GO

INSERT INTO UserBalance
VALUES
('2022-07-06', 'John Doe', '-100'),
('2022-07-05', 'John Doe', '-100'),
('2022-07-04', 'John Doe', '-100'),
('2022-07-03', 'John Doe', '-100'),
('2022-07-02', 'John Doe', '-100'),
('2022-07-01', 'John Doe', '20'),
('2022-06-30', 'John Doe', '-300'),
('2022-06-30', 'John Doe', '538')

SELECT
    UserName,
    DATEDIFF(DAY, MAX(Date), CAST(GETDATE() AS date)) AS 'Days Negative Balance'
FROM UserBalance
WHERE Balance >= 0
GROUP BY UserName
ORDER BY 1

You can even create a function that accepts the UserName as the input and outputs the number of days their balance was negative.

CREATE FUNCTION GetDaysNegativeBalance
(
    @UserName AS VARCHAR(100)
)
RETURNS INT
AS
BEGIN
    RETURN
    (
        SELECT DATEDIFF(DAY, MAX(Date), CAST(GETDATE() AS date)) AS 'Days Negative Balance'
        FROM UserBalance
        WHERE Balance >= 0
            AND UserName = @UserName
    )
END
GO

--Call function
SELECT dbo.GetDaysNegativeBalance('John Doe') AS 'Days Negative Balance'

CodePudding user response：

Can you try this select statement:

SELECT DISTINCT sample_data.User, COUNT(sample_data.User) from sample_data where sample_data.Date 
BETWEEN '2022-07-01' AND CURRENT_DATE() 
AND sample_data.Balance < 0 group by sample_data.User;

Possible has a more elegant solution, but I believe it captures your requirement. I also suggest adding expected output to your question to have better reference for solving it too.

Here is my sample output(sample_data is my table name for my replication and dev-1... are the users):

CodePudding user response：

Following gives negative for multiple user of multiple date-ranges.

Using below as data -

WITH input_table AS (
select '2022-07-06' as date1,'John Doe' as user, -100 as balance union all
select '2022-07-05','John Doe',-100 union all
select '2022-07-04','John Doe',-100 union all
select '2022-07-03','John Doe',-100 union all
select '2022-07-02','John Doe',-100 union all
select '2022-07-01','John Doe',20   union all
select '2022-06-30','John Doe',-300 union all
select '2022-06-30','John Doe',538 union all
select '2022-05-31','John Doe',-300 union all
select '2022-05-30','John Doe',-538 union all
select '2022-05-29','John Doe',300 union all
select '2022-07-03','Jack',-100 union all
select '2022-07-02','Jack',-100 union all
select '2022-07-01','Jack',20   union all
select '2022-06-30','Jack',-300 union all
select '2022-06-30','Jack',538
),

Query -

cte_1 as 
( select user, date1, 
lag(date1) over (partition by user order by date1 desc) as new_date1,
balance 
from input_table where balance>0)
select c.user, c.date1 as Negative_balance_since_date, 
(select count(*) from input_table t 
where t.balance<0 
and t.date1 > c.date1 
and (c.new_date1 is null or t.date1 < c.new_date1) 
and t.user = c.user)  as Number_of_days_since_negative_Balance
from cte_1 c;

Gives following output -