SQL - retrieving first entry for every user on a given day 1

[LGJ]

Hi,

I need some help please on building an SQL query. Bear with me as I attempt to explain below:

We have users attempting a purchase of a product, if they fail due to insufficient credit an entry is input in a DB table call rev_loss.

Here is an example of a row in the table:

created_t = 1077399562
account_num = 12345
event_price = 5
current_bal = 49
credit_limit = 50

I would like to total up the cost (event_price) of all the transactions for a given day (created_t > xxx and created_t < xxx) BUT for only the FIRST attempted purchase by each account_num.

I need to write the query in SQL no PL/SQL please.

Thanks
LGJ

 

[LKBrwnDBA]

Try this:

select sum(event_price) 
from (
select event_price
     , row_number ()
       over (partition by account_num
             order by created_t) rn  
from rev_loss)
where rn=1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LGJ]

Thank you very much that is great.

I have changed it slightly to also give a time range:

select sum(event_price)
from (
select event_price
, row_number ()
over (partition by msisdn
order by created_t) rn
from ouk_revenue_loss_t
where created_t > 1105605720
and created_t < 1105606980)
where rn=1;

Not really sure how the over (partition) bit works though, any simple explanations?

Have a star

Thanks
LGJ

 

[LKBrwnDBA]

Over (partition ... will assign the function (row-number) re-numbering from 1 starting at each group in the partition as ordered in 'order by' clause.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb