Latest / VoraPrep Audio — Professional Licensure & Certification Exam Prep / [CPA] ISC: SQL and Data Analytics for Auditors
Transcript
- Marcus: Welcome to VoraPrep Audio’s Office Hours. I’m Marcus. Ava: And I’m Ava. Today we’re talking about a topic that’s become a core competency for modern auditors. We're covering how to use sequel for data analytics. Marcus: Right. Auditors hear about big data and analytics all the time, but this is where it gets practical. Why is sequel specifically such a big deal for an audit? Ava: It fundamentally changes the scope of our testing. Think about it this way. In the past, if a company had a million invoices, an auditor might test a sample of a few hundred. That means you’re testing less than one percent of the population and extrapolating from there. Marcus: Which means you’re accepting a certain level of risk that you missed something in the other 99.9 percent. Ava: Exactly. With sequel, we can test one hundred percent of those million invoices. We can write a query that tells the database to check every single transaction for specific anomalies. And it can do that in minutes. This turns the audit from a limited spot-check into a comprehensive data investigation. Marcus: So instead of looking for a needle in a haystack, you’re using a giant magnet. Where does an auditor even start? What's the main tool within sequel? Ava: The main tool is the SELECT statement. It’s part of what’s called the Data Query Language, or DQL.. Its entire job is to ask the database questions and retrieve data. Other sequel commands exist, but for an auditor, the SELECT statement is the real workhorse. Marcus: Okay, I’m looking at a sample SELECT statement here. It has a lot of parts. SELECT, FROM, WHERE, GROUP BY... Can you walk me through what the key pieces are doing? Let's start with FROM. Ava: The FROM clause is the first thing you should look at. It tells you which table, or tables, the data is being pulled from. Think of it as choosing the right filing cabinet. In our example, it says FROM invoices, so we know we’re working with invoice data. Marcus: And then SELECT lists the specific columns you want to see? Ava: Precisely. It’s like telling the system which folders you want from that filing cabinet. Here, we're asking for the vendor's name, a count of their invoices, and the total sum of the invoice amounts. Marcus: Okay, so FROM picks the table, SELECT picks the columns. What does the WHERE clause do? Ava: WHERE is your filter. It sets the conditions for which individual rows to include. Before the database does any counting or summing, the WHERE clause sifts through every single row in the invoices table. It only keeps the ones that meet your criteria. Marcus: And in the example, the criteria is invoice date BETWEEN January 1st, 2026 AND December 31st, 2026. So it's only looking at invoices from that specific year. Ava: Correct. It throws out any invoice from 2025 or any other year before it moves on. Marcus: So after it filters the rows with WHERE, I see a GROUP BY clause. What's its role? Ava: GROUP BY takes all those individual rows that made it past the WHERE filter and bundles them into buckets. In this case, it says GROUP BY vendor name. So it creates one bucket for each vendor. Then it puts all of that vendor's 2026 invoices into their bucket. Marcus: Ah, and that’s why the SELECT statement can then use functions like COUNT and SUM. It’s counting the invoices and summing the amounts inside each of those vendor buckets. Ava: You’ve got it. Those are called aggregate functions, and they only work because GROUP BY created the groups for them to operate on. Marcus: Then there’s another filter, HAVING. How is that different from WHERE? They sound like they do the same thing. Ava: This is a very common point of confusion. They both filter, but they filter at different stages. WHERE filters individual rows before they get grouped. HAVING filters the entire groups after they’ve been created and aggregated. Marcus: So WHERE decides which players get to be on the field, and HAVING decides which teams get to stay on the field after the game is played. Ava: That’s a great way to put it. In our example, the clause is HAVING SUM of invoice amount is greater than 50,000. This means we only want to see the final results for vendors whose total invoice amount for the year was over fifty thousand. If a vendor’s total was forty thousand, their entire group is discarded. Marcus: And you couldn't put that SUM condition in the WHERE clause, could you? Ava: No, the query would fail if you tried. The WHERE clause screens individual rows, and an individual row doesn't have a sum. That sum only exists after the GROUP BY clause has done its work. Just remember: WHERE is for rows, and HAVING is for groups. Marcus: That’s a critical distinction. So, with this power to query everything, what are some of the specific audit tests we can run? Ava: We can perform some very powerful data analytics. For example, we can do outlier detection. Marcus: What would be an example of an outlier an auditor would look for? Ava: Think about payments made on a weekend or a holiday. Or a sales transaction with an unusually large discount percentage. We can use the WHERE clause to write a simple rule. Then we can have sequel scan millions of records for just those transactions that break the pattern. Marcus: So it automates the hunt for anomalies. What other techniques are common? Ava: Stratification is another big one. This is where we use GROUP BY to separate the entire population into risk-based categories. Marcus: You mean like grouping all transactions by dollar amount? Say, small, medium, and large? Ava: Exactly. Or you could group them by user, or by geographic location. This allows us to focus our attention on the highest-risk strata. We're not just taking a random sample from the whole pot anymore. Marcus: That makes sense. What about testing for completeness? Can sequel help us find things that are missing? Ava: Absolutely. That’s called gap and duplicate testing. We can ask sequel to check for breaks in sequential numbers, like missing check numbers or purchase order numbers. On the flip side, we can also have it look for duplicate invoice numbers being paid to the same vendor. Marcus: I've also heard about using Benford's Law. Can sequel handle that? Ava: It can. Benford's Law is a fascinating technique. It analyzes the frequency of the first digits in a set of numbers. The law states that in many naturally occurring datasets, the number one appears as the leading digit far more often than the number nine. If your data shows a different pattern from this expected distribution, it could suggest manipulation. And sequel can calculate that distribution for you. Marcus: So you can get a high-level indicator of fraud without even knowing what you're looking for. One more—what about trend analysis? Ava: That's a classic. We can use aggregate functions like SUM or AVG over time periods to spot unusual spikes or dips. Think about a sudden, sharp increase in sales to a new customer right at the end of a quarter. Or consider a strange drop in a specific expense category. sequel makes that analysis easy to perform across years of data. Marcus: Let’s make this concrete with a full audit scenario. How would an auditor use sequel to perform a three-way match? Ava: A three-way match is a perfect example. The audit objective is to test occurrence and accuracy. We need to make sure that for every invoice we paid, a valid purchase order exists. And we also need to confirm the goods were actually received. Marcus: So you’re matching the invoice to the goods receipt note and the original purchase order. You’d need data from three different tables. Ava: Correct. PurchaseOrders, GoodsReceipts, and Invoices. The key is how you join them together. The goal is to find exceptions, so we need to be very careful about our logic. Marcus: The example query uses a LEFT JOIN. Why is that specific join so important here? Ava: Because a LEFT JOIN is designed to find what’s missing. Think of it like a security guard at a building entrance. The guard has the master employee list. That’s our left table, the PurchaseOrders table. Then, the guard checks that list against the people who actually badged in today. That's the right table, like the Invoices table. Marcus: And the guard’s job isn't just to see who badged in. It's to find people on the master list who didn't badge in. Ava: Exactly. A LEFT JOIN keeps every single record from the left table. In our case, that's every purchase order. Then it tries to find a match in the tables to its right. If it can't find a match, it just leaves a blank, a NULL value. Those nulls are the red flags. They show us where the process broke down. Marcus: So if we see a purchase order with a NULL for the invoice number, it means the PO was never invoiced. Ava: Or if we see an invoice paid but a NULL for the goods receipt, we might have paid for something we never received. This is what we’re hunting for. Marcus: What would happen if you used an INNER JOIN instead? I see that one a lot too. Ava: An INNER JOIN would be the wrong tool for this specific job. An INNER JOIN only returns records where a match exists in all the tables. It’s designed to find perfect matches. Marcus: So it would only show you the transactions that worked correctly—the ones that have a PO, a receipt, and an invoice. Ava: And you would completely miss the most critical exceptions. You’d be blind to the very problems you’re supposed to be looking for. For completeness testing, for finding what’s missing, the LEFT JOIN is essential. Marcus: So for the exam, when we see a question with an sequel query, we're not expected to write it from scratch. We have to interpret it. Is there a framework for that? Ava: Yes, there's a simple four-step process. Step one is to identify the audit objective. Read the question prompt carefully. Ask yourself, what are they looking for? Is it completeness? Occurrence? Accuracy? Marcus: Step one: what is the auditor trying to accomplish? What’s next? Ava: Step two is to deconstruct the query’s key clauses. Look at the FROM clause to see the tables involved. Then, focus on the WHERE or HAVING clause. That’s the heart of the test. It tells you exactly how the records are being filtered. Marcus: And step three would be about the joins. Ava: Right, step three is to pinpoint the join logic. Is it an INNER JOIN, which looks for perfect matches? Or is it a LEFT JOIN, which hunts for mismatches and missing items? This is often the key to the whole question. Marcus: And the final step? Ava: Step four is to predict the output. Based on the objective, the filters, and the join logic, ask yourself: what kind of records will this query return? The correct answer choice will be the one that accurately describes what the sequel statement identifies. That could be a list of exceptions or a specific population. Marcus: That framework seems like it would keep you from getting lost in the syntax. And speaking of syntax, there's a specific logical order for how the database executes these clauses. It's not always intuitive. Ava: That’s true, and it explains some common errors. For instance, the FROM and WHERE clauses are processed long before the SELECT clause. There’s a helpful mnemonic to remember the logical execution order. Marcus: What is it? Ava: Friendly Whales Go Hunting Side-by-Order. Marcus: Friendly Whales Go Hunting Side-by-Order. Let me guess. FROM, then WHERE, GROUP BY, HAVING, SELECT, and finally ORDER BY. Ava: You got it. Knowing that helps you understand a common error. For example, you can’t use a column alias from the SELECT list down in your WHERE clause. Why? Because the WHERE clause was already processed long before the alias was even created. Marcus: That’s a great way to remember it. The logic here is so important. Breaking it down step-by-step is the only way to really get comfortable with it. Okay, Ava, final thought for us. On exam day, a candidate sees a big block of sequel code. What is the one thing they should do to stay calm and find the answer? Ava: Don’t get bogged down in the complex syntax. Ignore the parts you don't immediately recognize and focus on the main clauses we discussed. Ask yourself: What table is it pulling FROM? How is it filtering with WHERE? What is the JOIN logic? Answering those three questions will almost always reveal the query's audit objective. And that is the key to the right answer. Marcus: Excellent advice. Focus on the objective, not just the code. That’s all the time we have for Office Hours today. Thanks, Ava. Ava: Anytime, Marcus. Marcus: VoraPrep is a full exam-prep app — lessons, practice questions, and an AI tutor in one place. Get started at Vora Prep dot com.