This is Exactly How I Use SQL at Work

Ever wonder how DA and DS navigate the world of analytics? Imagine a product team, laser-focused on keeping customers happy during outages. From building apps to streamlining restoration services — all to minimize disruption. As their Data Scientist, guess what my secret weapon is? (Spoiler alert: it’s not Python!)

The answer: SQL.

This language, despite being old-fashioned, is my key to unlocking insights, nice and quick, from the data warehouse (Azure Synapse/Azure SQL DB depending on the analysis).

Different ways DA/DS interacts with Data Warehouse. Image by Author.

In this article, I will showcase how I used SQL to conquer a real-world business problem. We’ll delve into:

• The challenge our team faced
• My data-driven hypothesis
• The specific dataset involved
• The SQL queries that unlocked the answers

Hope this post will not only give you industry insights but also a boost of dopamine to grind SQL.

Let’s dive in!

1. The business problem

Let’s recall the team goal. We focus on transforming customer experiences with outages via product development and operational improvement.

With data-driven insights, our team has slashed customer calls to half. However, I have seen call metrics emerge into a stable trend.

Trend visualization. Source: Seascape models

Here are 2 possible scenarios:

• Has the feature adoption reached saturation? That means, no matter what feature we deliver next, customers of a particular demographic group just prefer calls over mobile/web apps.

Or…

• Do we still underserve the customers with all the features we provide them?

I leaned towards the second one.

2. My hypothesis

So, my hypothesis was:

We are underserving customers.

This was a generic hypothesis but a good start. To efficiently tackle the problem we had, I refined it further:

A small group of customers make up more than 80% of total phone calls. And this is the group that needs more help from us.

To test the hypothesis, I needed the following data:

• call records at the customer level
• data spanning from 2022, when we started to see traction with the first feature
• call records must have outage event info and call details, including the call content and the caller info, for further deep dive if my hypothesis is true

3. The dataset

Knowing exactly what I needed, I set out to explore the database. The client I’m working for didn’t have good database documentation, so I worked with a data engineer to gather the necessary info.

Here are the 2 data tables that look potential:

• call table: call records from the last 2 decades at customer level with details into the caller, call content, and corresponding outage event — Fantastic! A promising data table!
• outage events: outage details at event level — Sweet!

mock-up data tables (to protect client data)

With a few queries, I confirmed these were the tables I could use.

Alright, let’s get the ball rolling.

4. The queries

4.1 Find the percentage of customers who made 80% of total phone calls

A few reasonings before I started to pull the numbers:

• I was interested in phone calls only, either automated or agent calls, as these calls were costly to the business.
• If I justified the hypothesis, I’d dive deeper into the call records and need none-null data in caller and event info, which meant removing missing data from the analysis. (if you have more than 5% of missing data, though, be careful!)

This is the full query:

--find the percentage of customers who made more than 80% of phone calls over the past 2 years
--find running total calls by customers
WITH calls AS (
    SELECT account_num 
        , sum(count(*)) over (order by count(*) desc) as rolling_total
    FROM call_records
    WHERE year(input_time) BETWEEN 2022 AND 2023
        AND event_id != 0
        AND account_num is not null
        AND (user_name LIKE 'E%' OR user_name LIKE 'e%' OR user_name LIKE 'c%'
            OR (user_name LIKE 'C%' AND user_name != 'COF')
            OR user_name = 'IVR')
    GROUP BY account_num
)

--find the percentage of customers who made up over 80% of total phone calls
SELECT (count(*)+1)* 100.0 / (SELECT count(*) FROM calls) as pct
FROM calls
WHERE rolling_total < 0.8 * (SELECT max(rolling_total) from calls)

Now, the fun part. Can you guess how long it ran?

10 seconds! It computed across 4 million calls and gave me the answer in just a sip of coffee. Who doesn’t like that?

And oh, my hypothesis was true. It’s time to dig deeper.

Digging, digging deeper. Source: Sophie Graves

4.2 What can we do to serve the group better so they don’t need to make a call?

To solve this problem, I needed to figure out their motivations for calling. Here, I broke down the problem into small solvable questions:

• Did they experience longer, more frequent outages than the other group, so more calls?
• In what areas did we fail to serve them with our features?

Let’s look at the first question. Here’s the query to answer it:

--zoom in on outage length and frequency each group of customers experienced over past 2 years
--find running total calls (%) by customers
WITH calls AS (
    SELECT account_num 
        , count(DISTINCT c.event_id) as total_outages
        , avg(datediff(minute, begin_time, restore_time)) as length
        , sum(count(*)) over (order by count(*) desc) * 1.0 / sum(count(*)) over () as rolling_pct
    FROM call_records c
    JOIN outage o
    ON c.event_id = o.event_id
    WHERE year(input_time) BETWEEN 2022 AND 2023
        AND account_num is not null
        AND (user_name LIKE 'E%' OR user_name LIKE 'e%' OR user_name LIKE 'c%'
            OR (user_name LIKE 'C%' AND user_name != 'COF')
            OR user_name = 'IVR')
    GROUP BY account_num
), 
rolling_previous AS (
    SELECT *, lag(rolling_pct,1,0) over (order by rolling_pct) as prev_pct FROM calls
)

--find the frequency and length of outage events for each group
(SELECT 'active callers' as group
    , avg(length) as avg_length
    , avg(total_outages) as avg_frequency
FROM rolling_previous
WHERE prev_pct < 0.8)
UNION 
(SELECT 'non-active callers' as group
    , avg(length) as avg_length
    , avg(total_outages) as avg_frequency
FROM rolling_previous
WHERE prev_pct >= 0.8)

The active callers didn’t experience much longer outages than the other group but had substantially more outages over the past 2 years.

mock-up of the output (to protect client data)

Coming to the second question — in what area did we fail to serve them with our apps?

-- find most popular reasons for calling
SELECT substring(op_comment,1,5) as call_content, 
    count(*)*100.0 / sum(count(*)) over () as pct 
FROM rolling_previous c
JOIN call_records r ON r.account_num = c.account_num
WHERE prev_pct < 0.8
GROUP BY substring(op_comment,1,5) 
ORDER BY pct DESC

It turned out the ETA. Most of the calls were to get just that.

mock-up of output (to protect client data)

Alright, then I knew we had both problems with the grid system (frequency of outages) and restoration services and/or data pipelines (outage updates).

Note: Outage apps are data apps at their core, which stream updates to customers during outages. So, if customers are calling for updates, we can’t rule out the possibility of a data pipeline problem.

4.3 Query Optimization

You notice the CTEs (common table expressions) are quite similar in each of the queries, right? It’s best practice to create a view in such scenario, so you don’t need to rewrite it over and over again. Here’s the code to do so.

Also, views won’t take a physical place in the database and thus won’t impact the performance. You still have the 10-second-run luxury!

--create view for re-use
CREATE OR REPLACE VIEW rolling_previous AS
WITH calls AS (
    SELECT account_num 
        , count(DISTINCT c.event_id) as total_outages
        , avg(datediff(minute, begin_time, restore_time)) as length
        , sum(count(*)) over (order by count(*) desc) * 1.0 / sum(count(*)) over () as rolling_pct
    FROM call_records c
    JOIN outage o
    ON c.event_id = o.event_id
    WHERE year(input_time) BETWEEN 2022 AND 2023
        AND account_num is not null
        AND (user_name LIKE 'E%' OR user_name LIKE 'e%' OR user_name LIKE 'c%'
            OR (user_name LIKE 'C%' AND user_name != 'COF')
            OR user_name = 'IVR')
    GROUP BY account_num
)
SELECT *, lag(rolling_pct,1,0) over (order by rolling_pct) as prev_pct FROM calls

5. Wrapping up

From database to insights. Image by Author (DALL-E).

Now, do you still think SQL isn’t your everyday hero? This is just a taste of how this trusty language can hack its way through real-world problems. Get ready to unleash the power of data manipulation!

I hope this example of using SQL for real-world problem-solving piqued your interest. Mastering even basic SQL goes a long way.

That’s it for today.

Stay healthy and crunch data!

If you find this post helpful and interesting, please give it a like and share it with others who might like it too.

Let me know in comment the problems your SQL kills! And don’t forget to follow me for more data stories.

Thank you for reading!