199|SQL – LOTR Ledgers

Challenges
,
1

BYU Student Author: @Trent_Barlow
Reviewers: @Carter_Lee, @Marta_Ellsworth
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

Overview
Welcome to Middle-earth, where the grand production of the Lord of the Rings original trilogy is underway. As an accountant on this epic production, you were tasked with managing the financial transactions related to the filming process. Your ledger is a treasure trove of transactions that detail the enormous costs of creating such a cinematic masterpiece—from constructing sets in the Shire to the intricate costumes of the Elves. Now that the production is over and the studio is releasing the movies, Peter Jackson wants you to look back over the financial data and create some SQL scripts to help analyze the ledger.

To ensure the production stayed on budget and every dollar was accounted for, you must wield the power of SQL to navigate through this financial landscape. Because there is so much data, you take a sample of transaction from 2003 to work with while creating the SQL queries that you will run on the entire ledger.

Instructions

  1. Begin by examining the Business_transactons_sample_lotr table, which includes fields for transaction_id, date, description, amount, and category. Your first quest is to retrieve all transactions within the Utilities Category that occurred in April.
  2. Peter is worried they overpaid for utilities. Forge a SQL query that sums up the total Utility expenses (evidenced by negative amounts, and the category of Utilities) for the whole year, separated by the description of each Utility. How many descriptions are there? Do you notice any that may need to be reclassified as expenses rather than utilities?
  3. Finally, create a SQL query that will indicate the profitability each quarter by summing the amount colum and grouping and ordering by quarter. Hint: you will need to create ‘quarter’ using the date column. Is profitability going up or down?

Data Files

[details = “Suggestion and Hints”]

  1. It is recommend to attempt this query without looking at the hint.
    SELECT transaction_id, date, description, amount
    FROM Business_transactions_sample_lotr
    WHERE category = ‘Utilities’ AND DatePart(‘m’, [date]) = 4;
  2. It is recommend to attempt this query without looking at the hint.
    SELECT description, SUM(amount) AS utility_expense
    FROM Business_transactions_sample_lotr
    WHERE amount < 0 AND category = ‘Utilities’
    GROUP BY description
    ORDER BY SUM(amount);
  3. It is recommend to attempt this query without looking at the hint.
    SELECT DatePart(“q”, [date]) AS quarter, SUM(amount) AS total_income
    FROM Business_transactions_sample_lotr
    GROUP BY DatePart(“q”, [date])
    ORDER BY DatePart(“q”, [date]);

[/details]

Solution

[details = “Solution Code”]
SELECT transaction_id, date, description, amount
FROM Business_transactions_sample_lotr
WHERE category = ‘Utilities’ AND DatePart(‘m’, [date]) = 4;

SELECT description, SUM(amount) AS utility_expense
FROM Business_transactions_sample_lotr
WHERE amount < 0 AND category = ‘Utilities’
GROUP BY description
ORDER BY SUM(amount);

SELECT DatePart(“q”, [date]) AS quarter, SUM(amount) AS total_income
FROM Business_transactions_sample_lotr
GROUP BY DatePart(“q”, [date])
ORDER BY DatePart(“q”, [date]);
[/details]
Solution Video: Challenge 199|SQL – LOTR Ledgers