Question

For the below `bank' schema: customer(customerid,username,fname,lname,street1,street2,city,state,zip) account(accountid,customerid,description,) transaction(transactionid,trantimestamp,accountid,amount) A customer may have several accounts, and each account may participate in many transactions. Each transaction will have at least two records, one deducting amount from an account, and one adding amount to an account (for a single transactionid, the sum of amounts will equal zero). Using SQL, answer these questions (write a SQL query that answers these questions):

8) List the top 1% of customers (ordered by total balance).

9) Using balances for previous two months, predict what the balances will be next month. (tip: find slope of a line; x-axis is days, y-axis is balance. 2 previous months means you have 2 points, finding slope is easy. Use slope to predict where next month's balance will be.)

Answer 1

select Top round(custcount*0.01,1) customerid, username from (

select c.customerid,c.username,count(c.customerid) as custcount from customer c inner join account a on c.customerid=a.customerid inner join transaction t on a.accountid=t.accountid group by c.customerid,c.username order by sum(t.amount)) as subtable;

Tip command to get number of rows from results.custcount*0.01 gives 1% and round will do rounding to upper value.

as per HomeworkLib rules we can only solve 1 questions per post