customer(custid,fname,lname,ssn)
address(addid,custid,street,city,state,zip)
phone(phoneid,custid,num,type)
account(accntid,custid,type)   --accntid=1 is 'cash'
transctn(trid,tim,accntid,amnt)


create sequence custseq;
create sequence addressseq;
create sequence phoneseq;
create sequence accntseq;
create sequence trseq;

--create a new customer
insert into customer(custid,fname,lname,ssn)
 values(nextval('custseq'),'John','Doe','123-45-1234');
insert into address(addid,custid,street,city,state,zip)
 values(nextval('addressseq'), currval('custseq'),
   '2020 Bedford Ave','Brooklyn','NY','11210');
insert into phone(phoneid, custid,num,type)
 values(nextval('phoneseq'),currval('custseq'),
  '718-555-1234','H');

-- add checking account for customer 12345;
insert into account(accntid,custid, type)
 values(nextval('accntid'), 12345, 'C');

-- account 12345 gets deposit $100.
-- add money to customer's account
insert into transctn(trid,tim,accntid,amnt)
  values(nextval('trseq'), now(), 12345, 100);
-- take money out of 'cash' account.
insert into transctn(trid,tim,accntid,amnt)
  values(currval('trseq'), now(), 1, -100);

nextval('trseq') = 1
nextval('trseq') = 2
nextval('trseq') = 3
currval('trseq') = 3
currval('trseq') = 3
nextval('trseq') = 4



--find the balance for account 12345 ?

select sum(amnt)
from transctn
where accntid=12345;


-- find balance for ALL accounts...
select accntid,sum(amnt)
from transctn
group by accntid


-- find closing balance for all accounts on 2018-12-31


select accntid,sum(amnt)
from transctn
where tim<'2019-01-01'
group by accntid


-- find closing balance for customer 123 on 2018-12-31

select b.custid,sum(a.amnt) bal
from transctn a
  inner join account b
  on a.accntid=b.accntid
where b.custid=123 and a.tim<'2019-01-01'
group by b.custid


-- lets find corrupt transactions
-- (those that don't sum to 0).
select trid
from transctn
group by trid
having sum(amnt) != 0



-- find suspicious customers
--(customers with same ssn, but different names).
-- find instances of same ssn, but different names.

select ssn
from customer
group by ssn
having min(concat(fname,',',lname)) != max(concat(fname,',',lname))

-- find balance for each account for each day of 2018


with dates as (
  SELECT cast( date_trunc('day', dd) as date) as dt
   FROM generate_series
        ( cast('2018-01-01' as timestamp)
        , cast('2008-12-31' as timestamp)
        , cast('1 day' as interval) ) dd
),
accntdt as (
  select accntid,dt
  from account, dates
),
tamntday as (
  select
    coalesce(a.accntid,b.accntid) accntid,
    coalesce(a.dt, cast(b.tim as date) ) dt,
     sum( coalesce(b.amnt,0) ) amnt
  from accntdt a
    full outer join transactn b
    on a.accntid=b.accntid and cast(b.tim as date)=a.dt
  where coalesce(b.tim,'2018-01-01')<'2019-01-01'
  group by coalesce(a.accntid,b.accntid),
    coalesce(a.dt, cast(b.tim as date) )
),
bals as (
  select a.*,
   sum(amnt) over (partition by accntid order by dt) bal
  from tamntday a
)
select accntid,dt,bal
from bals
where dt>='2018-01-01' and dt<'2019-01-01';




-- find outlier transactions
-- transactions >= 2sd of 20 day moving average.


with dttransactions as (
  select accntid,cast(tim as date) dt,
    avg( sum(amnt) ) over (partition by accntid order by cast(tim as date) rows between 20 preceding and current row) avg20,
    stddev( sum(amnt) ) over (partition by accntid order by cast(tim as date) rows between 20 preceding and current row) sd20
  from transactn
  group by accntid,cast(tim as date)
)
select a.accntid,a.tim,b.dt,b.avg20,b.sd20,a.amnt
from transactn a
   inner join dttransactions b
   on a.accntid=b.accntid and cast(a.tim as date)=b.dt
where a.amnt >= b.avg20+b.sd20*2.0













customer(custid,fname,lname,ssn)
address(addid,custid,street,city,state,zip)
phone(phoneid,custid,num,type)
account(accntid,custid,type)   --accntid=1 is 'cash'
transctn(trid,tim,accntid,amnt)