When learning a new skill, it is always important to practice, practice and practice! Thus, I have been using the Preppin' Data Challenges to hone my skills in SQL and Snowflake. These are weekly exercises to help you learn and develop data preparation skills. The challenges are designed for learning Tableau Prep but they can be completed in R, Python, SQL, DBT, EasyMorph and many other tools.
To start, I will be completing challenge 2023- Week 3. This challenge involves comparing the bank transactions to target figures. The link to the challenge is: https://preppindata.blogspot.com/2023/01/2023-week-2-international-bank-account.html
To start, I brought the data in to inspect it, using the select * to bring in all columns from the tables. This challenge has two inputs, but we can join them using the ONLINE_OR_IN_PERSON and TRANSACTION_DATE columns, but we need to do some transformations first!
Transforming the transaction table
The first few cleaning steps are similar to previous challenges:
- Splitting the transaction codes by the first '-' to get the bank
- Changing Online and In-Person from 1 and 2, respectively
- Adding up all transactions using sum(value)
However, we want this dataset to be at the quarterly level to match the quarterly targets data. Similar to before, we need to change the TRANSACTION_DATE field, which is currently a string to a date data type. To do this we use the to_date function followed by the format 'DD/MM/YYYY HH24:MI:SS'. To change this date to quarters, we wrap the to_date function in the quarter function. The whole expression is now called DATE.
The next requirement is that we only keep transactions from the DSB bank. To do this, we use 'where' to act as a filter for bank='DSB', this occurs after we bring the data in (select/from).
Finally, we want the data to look at the total transactions for each transaction type and quarter. Therefore, we group by the bank, online_or_in_person and date.
Transforming the targets table
We need to unpivot (columns-to-rows) the Q1, Q2, Q3, Q4 so we have one uniform date column. To do this, we use the unpivot function. We structure an unpivot step like this:
In English, we creating a new QUARTER field for TARGET by unpivoting Q1, Q2, Q3, Q4.
We now have the ability to join on both quarter and transaction type. However, there are two ways.
Subquery vs CTEs
In SQL, subqueries and Common table expressions (CTEs) are used to break down complex queries (queries that include aggregations, transformations, filters etc), but they differ in implementation.
Subqueries
First, subqueries are implemented within another query. Using our example, we embedded the transformation that we did for the transactions table into the from section. The red section is identical to our original transformation and this whole table is now labeled t1.
We then embedded the transformation that we did to the targets table into the inner join section. The only difference between the original and the blue section is that we removed the Qs in all the QUARTERS field. We did this by using the right function, that retains the first character on the right (the number). This whole section is now labeled t2.
From there, we join on both transaction type (online_or_in_person) and quarter, retaining the table labels for each column. Finally in the order of execution, we then pick what columns we want to see returned which is the bank, (one of the) online_or_in_person fields, (one of the) date columns, transaction_value, and the variance between transaction_value and target which I've labeled vary.
CTEs
Common table expressions are very similar to subqueries. In our example, the transformations to our transactions and targets tables lay outside of the query itself. We then join the newly created tables t1 and t2 on the aforementioned fields.
Both methods result in the same output.
And that's it! This challenge has been good practice for joins and LODs, as well as practising subqueries vs CTEs. Personally, I prefer how CTEs are structed as I like to transform the different tables first and then combine together. I also think they are much easier to read and edit for future use!