SQL help

Database
Enthusiast

SQL help

Hi gurus, I am trying to generate a sql based on this scenario. please suggest how. i have a table with credit card number, transaction date and amount and merchant. I want to find a creditcard number which did transations in 3 consecutive months with same merchant with total amount >1000. eg:-a customer would have made transaction in walmart in oct, nov,dec and the total amount for 3 months should be > 1000. 



please suggest. 



Thanks

26 REPLIES
Junior Contributor

Re: SQL help

SELECT card_number,
merchant,
EXTRACT(YEAR FROM trans_date) AS trans_year,
EXTRACT(MONTH FROM trans_date) AS trans_month,
trans_year * 12 + trans_month AS trans_ym,
SUM(SUM(amount))
OVER (PARTITION BY card_number, merchant
ORDER BY trans_ym
ROWS 2 PRECEDING) AS sumamt
FROM trans
GROUP BY 1,2,3
QUALIFY sumamt > 1000
AND trans_ym - MIN(trans_ym) OVER (PARTITION BY card_number, merchant
ORDER BY trans_ym
ROWS 2 PRECEDING) = 2

This should be what you want: calculate the amount per month/creditcard/merchant and then check for three consecutive months and a sum(amount) > 1000.

Might return multiple rows per credit card, one row for each three months period meeting your condition.

Enthusiast

Re: SQL help

Thanks Dieter,

Wil try and post you the update..

Enthusiast

Re: SQL help

Dieter, I tried and iam getting this error. Failed 3504 I am getting selected non-aggregrated values must be part of associated group.

SELECT acct_id,

MRCH_NM,

EXTRACT(YEAR FROM trxn_post_dt) AS trans_year,

EXTRACT(MONTH FROM trxn_post_dt) AS trans_month,

trans_year * 12 + trans_month AS trans_ym,

SUM(SUM(trxn_amt))

OVER (PARTITION BY acct_id, MRCH_NM

ORDER BY trans_ym

ROWS 2 PRECEDING) AS sumamt

FROM POSTD_TRXN

GROUP BY 1,2,3

QUALIFY sumamt > 1000

AND trans_ym - MIN(trans_ym) OVER (PARTITION BY acct_id, MRCH_NM

ORDER BY trans_ym

ROWS 2 PRECEDING) = 2

Junior Contributor

Re: SQL help

Strange, this should work as-is. Maybe the parser is a bit confused, try GROUP BY 1,2,3,4

Btw, what's your TD release?

Enthusiast

Re: SQL help

Dieter, Actually it worked..

I gave GROUP BY 1,2,3,4,5

Re: SQL help

Wow, what an amazing site, this is my first visit and have already almost found the answer to my question.  I have a similar problem to code.  In my case I have ID, Date for customer service calls.  I need to flag those in the last year that called four times in a seven day window.

Data looks like this

ID Date

123 2013-02-03

456 2013-02-03

123 2013-02-04

123 2013-02-04

456 2013-02-05

456 2013-02-05

123 2013-02-09

456 2013-02-11

I would like to return the ID and last date of the 7 day window, for this data I would only want:

123 2013-02-06

It is likely that a customer has had this occurance multiple times over the year, not a problem as I could grab the ID and max(date)

Thank you in advance,

Bob

Re: SQL help

I apologize in advance, I would be looking for the following to be returned:

123 2013-02-09

Junior Contributor

Re: SQL help

Hi Bob,

no need to apologize :-)

Rephrasing your condition: Find rows where the number of days between the current date and the date three rows before is less than or equal to seven.

SELECT * FROM tab
QUALIFY
dt - MIN(dt)
OVER (PARTITION BY id
ORDER BY dt
ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) <= 7

Re: SQL help

Thank you Dieter!  I'm an analyst who has brute force coded in SQL for years and never had the elegance of the qualify/partition combo.  Thx!