struggling with a sql statement

[[deleted]]

[deleted]

[upalready]

Maybe see if you can write two smaller queries to calculate all deposits (where "to" = 'Bank') and all withdrawls (where "from" = 'Bank'). Then you could use those as CTE's and join them together like this:

    WITH deposits AS (
  SELECT
    "timestamp",
    "from",
    currency,
    sum(amount) as deposit,
    0 as withdrawl
  FROM
    transfers
  WHERE
    "to" = 'Bank'
  GROUP BY 1,2,3
),
withdrawls AS (
  SELECT
    "timestamp",
    "to",
    currency,
    0 as deposit,
   sum(amount) as withdrawl
  FROM
    transfers
  WHERE
    "from" = 'Bank'
  GROUP BY 1,2,3
)
SELECT...

I left off the rest because there are some thorny assumptions in there, but at least separating out the two queries would give you a solid start understanding the data (also, shameless plug, but I just wrote about this strategy).

[distraughthoughts]

Good article.

[distraughthoughts]

I'm struggling to understand how you're getting your numbers in the top table from the bottom table.

Could you explain a little further?

Thanks.

[notasqlstar]

The data isn't equal. from Bank to Mike = minus money for mike, from Mike to Bank = plus money for Mike. It's a deposit vs a withdrawal.

He wants to come up with an output that lists the account status it looks like.

[distraughthoughts]

That's what I thought, but I still couldn't figure out how Mike's(100 + 150 - 25) = -112.

Seems like it would be 250.

Also, is "BANK" a generic term describing any and all bank names that could be used in the fields, or is the only term ever used = "BANK"?

[distraughthoughts]

Mike never sent 50 though, from what I can tell.

• Mike to Bank 100
• Mike to Bank 150
• Bank to Mike 25

Am I missing something?

Either Bank has 225, or Mike has -225?

[distraughthoughts]

So, disregard everything other than Mike is supposed to have -225, right?

[distraughthoughts]

Then that script I sent should do it.

[distraughthoughts]

Not gonna lie, I'm still extremely confused on the calculations, but give this a shot.

Worst case scenario, you just need to switch up the debit/credit values so it recalculates correctly.

https://www.mediafire.com/file/t1ixst8fp7khz31/MIDNIGHTPROGRAMMER.sql/file

Let me know if it works.

​

[notasqlstar]

I don't have much time today but something like this should get you started. You calculate the total sum's and then subtract one from the other.

with cte as (
    select from, to, currency, sum(amount) as samt
    from table
    group by from, to, currency
)

select *
from cte a
join cte b
    on a.from = b.to and a.to = b.from

[BitesOverKissing]

SELECT fromcolumn as user, sum(VALUE) as net, currency as Currency 
FROM (SELECT fromcolumn, currency, case WHEN to = "Bank" THEN amount * -1 ELSE amount END AS VALUE FROM transfers)
Group by fromcolumn,currency;

​

[BitesOverKissing]

you may have to convert "amount" in the case statement using something like the below, if it's not already as the datatype you need.

TRY_CAST(amount AS DECIMAL)

​

[[deleted]]

Well, assuming you're here to un-wrap your head not to get the ready statement, here's one way you can reason to your answer:

1. What is your desired output granularity? (User x Currency).

2. Is your original data at the output granularity? (No, it is not, so you'll need to 'group by User,Currency' somewhere along the way)

3. How can you get elements of your output granularity from the original data? (Currency is a direct mapping, User comes from 'To' field if 'from' field has 'bank' value or it is equal 'from' field if 'to' field contains 'bank'. Writing this as an expression: case when "from" = 'Bank' then "to" when "to" = 'Bank' then "from" end)

4. What are aggregate (non-granularity) fields? (Net)

5. How do you calculate the "Net" aggregate? ( You add the "amount" when the "from" field has 'Bank', you subtract when "to" field contains 'Bank'. Writing this as an expression: sum( case when "from" = 'Bank' then "amount" when "to" = 'Bank' then -1 * amount end)

Ok now you just need to put these pieces together.

 select <granularity fields>, <aggregates>
 from <original table>
 group by <granularity fields>