Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
select ifnull(split.CATEGID, tran.CATEGID) as CATEGID, c.CATEGNAME as Category
, ifnull(split.SUBCATEGID, tran.SUBCATEGID) as SUBCATEGID, s.SUBCATEGNAME as Subcategory
, sum(ifnull(split.SPLITTRANSAMOUNT, tran.TRANSAMOUNT)) as [Total Spend]
, count(distinct strftime('%Y%m', tran.TRANSDATE)) as [Months Active]
, sum(ifnull(split.SPLITTRANSAMOUNT, tran.TRANSAMOUNT)) / count(distinct strftime('%m', tran.TRANSDATE)) as [Average Spending]
from CHECKINGACCOUNT_V1 as tran
left join SPLITTRANSACTIONS_V1 as split on tran.TRANSID = split.TRANSID
join CATEGORY_V1 as c on (c.CATEGID = ifnull(split.CATEGID, tran.CATEGID))
join SUBCATEGORY_V1 as s on (s.CATEGID = c.CATEGID and s.SUBCATEGID = ifnull(split.SUBCATEGID, tran.SUBCATEGID))
where tran.transcode = 'Withdrawal'
and tran.payeeid != 15
group by Category, Subcategory;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.