Translating Business Context into an Advanced SQL Query: A Starting Framework

How to Approach Coming Up with an Advanced SQL Query from a Business Context

Structured Query Language (SQL) is an excellent tool for analyzing data and drawing insights. It allows businesses to extract meaningful information from databases, which can help them make informed decisions. However, coming up with an advanced SQL query can be challenging, especially when we are dealing with complex data structures. There is often a gap between the educational paradigm (YouTube videos, courses, boot camps, etc) and the real-world practical SQL Maestro. Thus, it is worth discussing how one can approach creating advanced SQL queries from a business context, including specific examples of how to proceed from project inception to completion.

Understanding the Business Context

Before coming up with an advanced SQL query, it is essential to understand the business context. What is the real business problem at hand? A query can sit on its own and pull some data, but in reality, this tends to have little to no value without a clear and real alignment to the business need. This is the difference between the “Wow” and “So What?” responses received from clients, stakeholders, and internal teams.

There is a huge gap between “Wow”-ing and raising skepticism in the communication of Data Analysis.

For instance, suppose a business wants to analyze sales data to identify the most popular products. In that case, it is essential to understand what data is required to solve the problem, such as product IDs, sales figures, and customer demographics. However many people may stop at “column names” alone and not penetrate deeper into the crucial business logic behind the data. That is, what are the underlying assumptions, constraints, and rules that govern business operations?

Acquiring vital business context can be invaluable. Start by talking to subject matter experts, reviewing business documents, and observing how the business operates. This often appears to be a cursory step and something the less-experienced Data Analyst or Data Scientist may quickly gloss over. Understanding business context requires a level of curiosity to “dig into details” and the practice of artful listening.
What does this mean?

Let’s take the scenario of business sales data, which appears to be a rather run-of-the-mill use case: it is still worth spending time understanding the client’s goals with the data.

Consider the following questions to help you align with the business need:

• What are the goals of insight generation and analysis?
• How was the data gathered or acquired?
• What is the local team context in tandem with the broader organizational context?
• Are there any limitations on data access or privacy restrictions to be aware of?
• How is the data currently being used (if it is used at all)?
• Is the data actually the right information required to answer the expected targets of the analysis?
• What are the concrete business actions that a client (or internal team) hopes to achieve by conducting this analysis?

Take the time to think through and work through these questions with your team and stakeholders in order to front-load some of the work beforehand. Do not make the mistake of assuming that your sales team or pre-sales organization has done it for you. Here’s a relevant visual aid to illustrate the mismatch of point-of-view in many modern organizations.

The Difference in Perspective — Enterprise Edition. Source.

A separate article can (and should) be written about the details of business requirements gathering, but here’s a good resource for now.

After speaking with the client/stakeholders, here are some examples of the types of things we can learn:

• Before: We (the Sales team) want to increase sales.
• After: We (the Sales team) wanted to understand the driver of worsening online sales
• Before: “We want to understand the target demographics of online customers”
• After: “It looks like there’s an issue with the customer demographic data for online sales and/or it is not tracking all the metrics we thought were being tracked.”
• Before: “We want to find our high-value customers.”
• After: “We want to understand why traditionally high-value customers (in XYZ demographic) have suddenly stopped converting.”

Say it with me: “NUANCES MATTER!”

Practicing the Art of Listening, requires patience and collaboration, that we can skip over in our daily business habits.

Designing the Query: Break It Down

Once you have a clear understanding of the business context, you can start designing the SQL query. One way to do this is to break down the business problem into smaller parts and create SQL queries for each part. The following process might help:

Breaking down a Query

1) Consider the overall business goal or context (after sufficient digging mentioned above).

2) What are the logical sub-questions that need to be answered in order to answer the larger questions at hand?

3) How does the data map and help answer the broader context and each sub-question? Take notes and draw mind maps (on pen/paper or whiteboard)

4) Design some pseudo-code for each of the targeted subqueries in each sub-question, leaving room to integrate them later to answer main focus of the analysis (but always keep it in mind!)

5) Test on subsets of the data rather than the entire dataset to accelerate development before implementing it on the large database at scale

6) Consider operational query efficiency and explore what can be improved in order to improve query efficiency at a larger data volume.

7) Pay attention to new sub-questions (while avoiding “scope creep”) that can help answer the main question or topic of interest.

8) Test, refine, and iterate. Get buy-in or feedback when appropriate from the client team! This can depend on the willingness of the client to collaborate, but this is another reason that getting buy-in early is important because it helps motivate them to work together to produce a more fruitful result.

9) Combine outputs of sub-queries into an overall analysis addressing the main goal

10) Think carefully about how the results and analysis can help inform the central question and any sub-questions as well. This is the closing step of the loop, where we can affirm that the analysis performed really did align (or not) with the business need.

Before business context:

• Query overall sales data to get total revenue, units sold over time
• Look at sales by product category, segment by price, margin
• Query customer table to count new vs returning customers
• Analyze web traffic data volumes, top landing pages
• Identify customers with the highest lifetime value

This broad analysis may uncover some high-level trends but lacks a specific focus on the key questions and goals of the business stakeholders. There is no clear analytical narrative, beyond the generic industry approach.

With deeper business context:

• Targeted queries on online vs offline sales in order to uncover conversion issue
• Joining analytics data along with business context to unearth frictions for customers caused by the recent site redesign (leading to dropoffs)
• Business-specific goals lead to a tailored analysis of high-value customer behavior changes using specific types of subqueries
• New understanding of the truly high-value customers along with their hesitation to “convert.”

Advanced SQL queries can provide a wealth of insights and value to businesses, but they can be challenging to create. However, by understanding the business context, the creation of targeted queries involving subqueries, window functions, and common table expressions, becomes much more streamlined.

Data Science is an Art form. Created with DALLE3

Data Analysis and Data Science can be a type of art form, which can be danced through careful planning, testing, and validation to ensure the accuracy and reliability of the results. By understanding the business context, and using subqueries, window functions, and common table expressions, you can create advanced SQL queries that provide valuable insights into the client’s business. Remember that SQL is a powerful tool that can help you make informed decisions, but it requires a deep understanding of the data and business context to be utilized effectively.

The search for the right query in order to answer the correct context.

Conclusion

In conclusion, coming up with an advanced SQL query from a business context can be challenging, but it is essential for drawing insights and making informed decisions. Remember that SQL is a powerful tool, but it requires careful planning, testing, and validation to ensure the accuracy and reliability of the results.