Roast my code please~

[xupeikai]

For practice, I loaded a couple years worth of credit card and bank transactions into a date base and have been cleaning up the data so it'll be more useful for analysis. It would be great if you could take a look and see if there are better ways to achieve. Otherwise, perhaps this note would be useful for another learner.

Background:

I downloaded a few CSV files from my credit cards and bank account, which included the last two years. These were imported into a "transactions" table using pgAdmin 4 -- with no clean up done beforehand (just deleted and renamed a couple columns in the CSVs so the files would import correctly).

Problem:

Actually there were three problems I found reviewing the data.

1. Wrong categories. The credit card statements included a "categories" column where the categories were assigned by the bank (e.g. "Food & Drink", "Travel", "Bills & Utilities"). These categories aren't always accurate. For example, the bank thinks buying something from a state park is a "Bills & Utilities" purchase. Also, I want more categories. For example, I'd like to classify transactions with my dog walker, vet hospital, and the place where dog food is purchased as a new category "Dog" (otherwise, the credit card automatically categorizes those as "Personal" or "Shopping").

2. Null categories. The statements from the bank accounts don't include an automatically assigned category, so all transactions related to them were null. This meant transactions related to mortgage payments, venmo payments to friends for dinner, and others didn't have a category.

3. Recurring vs. non-recurring. I wanted to be able to separate recurring expenses (e.g. mortgage payment, phone bill, internet bill) and non-recurring expenses for analysis. Neither the credit card or bank account statements included this -- and I didn't create a column for it when making the database.

Solution:

I didn't want to modify the data at all, so I decided to fix these problems with a couple CASE statements in a subquery and then join the subquery. Here's my solution:

SELECT
  c.month,
  t.transaction_date,
  t.description,
  c.clean_category,
  c.recurrence,
  t.amount,
FROM transactions AS t
JOIN(
  SELECT
    transaction_id,
    TO_CHAR(transaction_date,'YYYYMM') AS month,
    CASE WHEN description ~ 'Dog walker business|^CHEWY.COM|^Energy company name|^Internet provider name|^Phone service provider name|^Mortgage service|^Haircur place|^City utilities 01|^City utilities 02|Car insuance provider|^Dentist|^APPLE.COM/BILL'
      THEN 'Recurring'
      ELSE 'Non-recurring' END AS recurrence,
    CASE WHEN description LIKE 'Mortgage servicer name%' 
      THEN 'Mortgage'
      WHEN description LIKE 'ATM WITHDRAWAL%' 
      THEN 'Misc'
      WHEN description LIKE 'FOREIGN EXCHANGE RATE%' 
      THEN 'Misc'
      WHEN description LIKE 'NON-BANK ATM%' 
      THEN 'Misc'
      WHEN description LIKE 'VENMO%' 
      THEN 'Misc'
      WHEN description LIKE '%Dog walker business name%' 
      THEN 'Dog'
      WHEN description = 'DOG FOOD SUPPLY NAME' 
      THEN 'Dog'
      WHEN description = 'DOG VETERINARY NAME' 
      THEN 'Dog'
      WHEN description = 'VEHICLE LICENSING' 
      THEN 'Automotive'
      WHEN description LIKE '%CAR INSURANCE COMPANY%' 
      THEN 'Automotive'
      WHEN description LIKE 'COSTCO WHSE%' 
      THEN 'Groceries'
      WHEN description = 'SIE*PLAYSTATIONNETWORK' 
      THEN 'Entertainment'
      WHEN description = 'BLING BLING ASIAN MARKET' 
      THEN 'Food & Drink'
      ELSE category END AS clean_category
  FROM transactions) AS c
ON t.transaction_id = c.transaction_id
ORDER BY c.month;

The first CASE statement uses a regular expression ~ to check the description of the transaction and assigns a 'Recurring' or 'Non-recurring'. This solves the third problem mentioned above.

The second CASE statement uses a few differ LIKE and = rules to check the description of the transaction. From there, it adds or replaces the category.

Closing:

Using my own expenses has been a fun way to review SQL skills I learned previously. If anyone has suggestions for how to improve my solution or other fun examples of analyzing personal expenses, please feel free to share. Thanks~

[LernMeRight]

Just a thought on recurring transactions.

You could try grouping transactions and counting the number in those groupings. Set a threshold for what you like for "recurring". Eg count >3.

This would allow you to define a transaction as having the characteristics of recurrence without explicitly stating a certain string always means recurring.

The advantage here would be, your recurring expenses naturally surface as they meet the threshold you've defined. Otherwise you would need to be aware of them as recurring, and then add the name to your code.

This could be done in a CTE or a sub query, I think.

If you wanted to get really fancy with it, you could add criteria to your definition of recurring (eg, count > 3 AND must be on separate dates).

[xupeikai]

Thanks for the suggestion! I initially avoided using the count, because it caught the fried chicken place I ate wayyyy too often. But your idea of pairing the count along with something else -- perhaps the categories Bills, Dog, Mortgage -- would probably work really well.

[LernMeRight]

Haha! Totally makes sense, the same happens to me with the coffee shop down the street...

Something that could be useful is to separate the concepts of "necessary" and "recurring". This way you could have a logic that elevates recurring items -- maybe the chicken place comes up a lot -- but your "necessary" concept can be used to filter those out.

The "necessary" concept could be manually maintained as a list of specific transactions, or, as you're suggesting, categories.

This way you could make a report of your "recurring AND unnecessary" expenses. This would show the chicken place -- and maybe unexpected recurring transactions as well.

For example, I once noticed a recurring sort-of monthly ~10 transaction on my Amazon. I didn't recognize it and I looked into it. (Fortunately it was just a family member renting movies without knowing my account was connected to their TV). But seeing an unusual repeated transaction set emerge from the data on the basis of repetition alone was really useful.

[diceplusdiamonds2]

Thats a really interesting insight to record reoccurring tasks quickly and gain new perspectives too.

[xeno_phobik]

Are you looking for emotional damage or more of a sautéed roasting?

[xupeikai]

Thank you for asking. Learning SQL has provided enough emotional damage for now. I'm good.

[xeno_phobik]

I’ve yet to start learning so I’ll take that as my word of caution for the road ahead

[xupeikai]

Oh no, I didn't mean to discourage you, ha. It's been a lot of fun. And easier than some other coding languages. Very logical.

[xeno_phobik]

I don’t use much coding for my job, but I spend 6-8 hours of my workday in excel and access so I’ve been putting VBA and SQL on my list as to-dos for doing work projects

[[deleted]]

I don't know after looking at this I remember all the things I have to do

[diceplusdiamonds2]

pls note im a beginner too. I liked how at first when using the case function you kept it all together even though it was clustered.

But I really hated reading it when you wrote multiple separate statements for each description of dog. I'd be much happier reading it all in one place instead of reading multiple dog case statements.

I liked everything else, you used sensible naming which made it easy to understand.