CODEWITHSUNDEEP
sql
user2900369
user2900369

Reputation: 809

Finding the first occurrence of an element in a SQL database

I have a table with a column for customer names, a column for purchase amount, and a column for the date of the purchase. Is there an easy way I can find how much first time customers spent on each day?

So I have 

Name    |     Purchase Amount | Date
Joe           10                9/1/2014
Tom           27                9/1/2014
Dave          36                9/1/2014
Tom           7                 9/2/2014
Diane         10                9/3/2014
Larry         12                9/3/2014
Dave          14                9/5/2014
Jerry         16                9/6/2014

And I would like something like

Date     |     Total first Time Purchase
9/1/2014       73
9/3/2014       22
9/6/2014       16

Can anyone help me out with this?

Upvotes: 1

Views: 2195

Answers (3)

user330315
user330315

Reputation:

The following is standard SQL and works on nearly all DBMS

select date, 
       sum(purchaseamount) as total_first_time_purchase
from (
   select date,
          purchaseamount, 
          row_number() over (partition by name order by date) as rn
   from the_table
) t    
where rn = 1
group by date;

The derived table (the inner select) selects all "first time" purchases and the outside the aggregates based on the date.

Upvotes: 5

markA
markA

Reputation: 1609

If you are using SQL Server you can accomplish this with either sub-queries or CTEs (Common Table Expressions). Since there is already an answer with sub-queries, here is the CTE version.

First the following will identify each row where there is a first time purchase and then get the sum of those values grouped by date: 

   ;WITH cte
    AS (
        SELECT [Name]
            ,PurchaseAmount
            ,[date]
            ,ROW_NUMBER() OVER (
                PARTITION BY [Name] ORDER BY [date] --start at 1 for each name at the earliest date and count up, reset every time the name changes
                ) AS rn
        FROM yourTableName
        )
    SELECT [date]
        ,sum(PurchaseAmount) AS TotalFirstTimePurchases
    FROM cte
    WHERE rn = 1
    GROUP BY [date]

Upvotes: 0

chezy525
chezy525

Reputation: 4174

The two key concepts here are aggregates and sub-queries, and the details of which dbms you're using may change the exact implementation, but the basic concept is the same.

  1. For each name, determine they're first date
  2. Using the results of 1, find each person's first day purchase amount
  3. Using the results of 2, sum the amounts for each date

In SQL Server, it could look like this:

select Date, [totalFirstTimePurchases] = sum(PurchaseAmount)
from (
    select t.Date, t.PurchaseAmount, t.Name
    from table1 t
      join (
        select Name, [firstDate] = min(Date)
        from table1
        group by Name
        ) f on t.Name=f.Name and t.Date=f.firstDate
    ) ftp
group by Date

Upvotes: 1

Related Questions