Get userwise balance and first transaction date of users in SQL-CodePudding

I have created a Transaction table with columns card_id, amount, created_at. There may be more than 1 row of one user so I want to return the value card_id, sum(amount), first created_at date of all users.

CREATE TABLE Transactions(card_id int, amount money, created_at date)
INSERT INTO Transactions(card_id, amount, created_at)
SELECT 1, 500, '2016-01-01' union all
SELECT 1, 100, '2016-01-01' union all
SELECT 1, 100, '2016-01-01' union all
SELECT 1, 200, '2016-01-02' union all
SELECT 1, 300, '2016-01-03' union all
SELECT 2, 100, '2016-01-04' union all
SELECT 2, 200, '2016-01-05' union all
SELECT 3, 700, '2016-01-06' union all
SELECT 1, 100, '2016-01-07' union all
SELECT 2, 100, '2016-01-07' union all
SELECT 3, 100, '2016-01-07'

I have created function for that but one of my client says I need query not function. Can anyone here suggest what query to use?

CREATE FUNCTION [dbo].[card_id_data]()
RETURNS @t TABLE
           (
                card_id text,
                amount money,
                dateOfFirstTransaction date
           ) 
AS
BEGIN   
    INSERT INTO @t(card_id)
        SELECT DISTINCT(card_id) FROM Transactions;
    
    UPDATE @t 
    SET dateOfFirstTransaction = b.createdat
    FROM 
        (SELECT DISTINCT(card_id) cardid, 
                MIN(created_at) createdat 
         FROM Transactions 
         WHERE amount < 0 
         GROUP BY card_id) b
    WHERE card_id = b.cardid;
    
    UPDATE @t 
    SET amount = T.AMOUNT
    FROM
        (SELECT 
             card_id AS cardid, SUM(MIN(AMOUNT)) AMOUNT, created_at 
         FROM Transactions 
         WHERE amount < 0  
         GROUP BY card_id, created_at) T
    WHERE card_id = cardid 
      AND dateOfFirstTransaction = created_at;

    RETURN
END

I want a result as shown in this screenshot:

CodePudding user response：

Try this:

/*
CREATE TABLE dbo.Transactions
(
    card_id INT,
    amount MONEY,
    created_at DATE
);

INSERT INTO dbo.Transactions (card_id, amount, created_at)
VALUES (1, 500, '2016-01-01'),
       (1, 100, '2016-01-01'),
       (1, 100, '2016-01-01'),
       (1, 200, '2016-01-02'),
       (1, 300, '2016-01-03'),
       (2, 100, '2016-01-04'),
       (2, 200, '2016-01-05'),
       (3, 700, '2016-01-06'),
       (1, 100, '2016-01-07'),
       (2, 100, '2016-01-07'),
       (3, 100, '2016-01-07');
*/

WITH FirstDatePerCard AS
(
    SELECT 
        card_id,
        FirstDate = MIN(created_at) 
    FROM 
        dbo.Transactions
    GROUP BY
        card_id
)
SELECT DISTINCT
    t.card_id,
    SumAmount = SUM(amount) OVER (PARTITION BY t.card_id),
    FirstDate = f.FirstDate
FROM 
    FirstDatePerCard f
INNER JOIN 
    dbo.Transactions t ON f.card_id = t.card_id AND f.FirstDate = t.created_at

You'll get an output something like this:

card_id   SumAmount   FirstDate
--------------------------------
   1        700.00    2016-01-01
   2        100.00    2016-01-04
   3        700.00    2016-01-06

Is that what you're looking for??

UPDATE: OK, so you want to sum the amount only for the first_date, for every card_id - is that correct? (wasn't clear from the original question)

Updated my solution accordingly

CodePudding user response：

You can use DENSE_RANK for this. It will number the rows, taking into account tied places (same dates)

SELECT
    t.card_id,
    SumAmount = SUM(amount),
    FirstDate = MIN(t.created_at)
FROM (
    SELECT *,
      rn = DENSE_RANK() OVER (PARTITION BY t.card_id ORDER BY t.created_at)
    FROM dbo.Transactions t
) t
WHERE t.rn = 1
GROUP BY t.card_id;

If the dates are actually dates and times, and you want to sum the whole day, change t.created_at to CAST(t.created_at AS date)