GGG
◂ posts7 min left

nl2sql7 min

GPT-4o’s Nl2SQL Blind Spot: Inaccuracies on Sales KPI Calculations

published
reading time
7 min
topics
nl2sql, llms, sales kpis

It’s unquestionable that LLMs know how to translate natural language into SQL code (NL2SQL). Over the past two years, LLM-based NL2SQL frameworks have demonstrated state-of-the-art performance. Notable examples include DAIL-SQL, DIN-SQL, and C3, which currently rank at the top of the Spider benchmark¹.

However, the translation of natural language into BI (NL2BI) remains an under-explored area.

One of the main practical applications of NL2SQL is calculating common sales KPIs, such as:

  • Year-over-year (YoY): Compares metrics from one year to the same metrics from the previous year, helping to identify growth trends and seasonal patterns.
  • Period calculations: Such as week-over-week (WoW) or day-over-day (DoD), these metrics compare the performance of a specific time period to the previous equivalent period, providing insight into short-term trends and fluctuations.
  • Year-to-date (YTD): Measures performance from the beginning of the current year up to the present date, offering a cumulative view of progress and helping to track annual goals.

These calculations form the backbone of simple interpretations of sales, units sold, conversion rates, and more. They are indispensable for making sense of data in real-world analytics.

One aspect not covered by the literature or benchmarks is the extremely poor performance of GPT-4 on basic calculations of sales KPIs. The goal of this article is to shed light on the performance of LLMs in generating robust SQL queries specific to common sales KPIs. This exploration aims to pinpoint the main challenges and suggest possible routes to mitigate them. For all experiments discussed, you will be able to test the results for yourself.

Experiments Set-up

To make the experiment straightforward and replicable, the queries were executed on a simplified version of the Sales Table used in my last Medium article. This table includes the following fields: date (purchase_date), categorical data (product_category), and numerical data (gross_sales). This simplification allows us to isolate and focus on specific elements of the experiment while reducing hallucinations by the LLMs.

The experiment has a simple go: to evaluate if GPT-4o is able to generate a robust SQL code to calculate YoY — a basic sales kpi metric. The question asked was:

What was the YoY sales by product_marketing_category on daily level?

You can copy and paste the prompt below and check it yourself the results.

Here’s the base prompt:

You're a specialist in generating SQL code in Google Big Query syntax from business question about the following table:
sales_table('purchase_date','product_marketing_category','gross_sales')

Here are the columns present in along with a descripition:
- purchase_date: The date when the purchase was made.
- product_marketing_category: The marketing category under which the product is classified.
- gross_sales: The total sales amount generated from the order.

Please, generate the SQL code to answer the following question:
Question: "What was the YoY sales by product_marketing_category on daily level?"

Baseline Prompt

The model confidently outputs the query below. The main problem here is that the LLM opted for using the LAG function. While this may seem logical at first glance, the LAG function can yield incorrect results when dealing with incomplete data, because it steps back a fixed number of rows rather than a fixed number of days. Missing dates are very common in real-world datasets: a category with no sales on a given day has no row for that day at all. In the example below, especially since we are dealing with “daily-level” granularity, this can lead to completely erroneous calculations.

Imagine if the goal was to compare June 9, 2024, to the same date last year (June 9, 2023), in other words YoY growth, but instead it compares to some earlier date. That’s exactly what’s happening in the query below. When dates are missing, LAG(total_sales, 365) doesn’t land on the same day last year: it lands 365 rows back, because it counts rows rather than actual dates.

WITH daily_sales AS (
    SELECT 
        purchase_date,
        product_marketing_category,
        SUM(gross_sales) AS total_sales
    FROM 
        sales_table
    GROUP BY 
        purchase_date,
        product_marketing_category
),
daily_sales_with_prev_year AS (
    SELECT 
        ds.purchase_date,
        ds.product_marketing_category,
        ds.total_sales AS current_year_sales,
        LAG(ds.total_sales, 365) OVER (PARTITION BY ds.product_marketing_category ORDER BY ds.purchase_date) AS prev_year_sales
    FROM 
        daily_sales ds
)
SELECT 
    purchase_date,
    product_marketing_category,
    current_year_sales,
    prev_year_sales,
    ((current_year_sales - prev_year_sales) / prev_year_sales) * 100 AS YoY_growth_percentage
FROM 
    daily_sales_with_prev_year
ORDER BY 
    purchase_date,
    product_marketing_category;

Adding a specific instruction to generate Robust SQL code.

An obvious path to solve this issues, is to add a explicit instruction guiding the model to write a robust SQL code. Thus, the following instructions was added at the end of the baseline prompt.

You must write robust SQL code capable of handling missing values \
and avoiding syntax errors.

The generated SQL code looks even worse than the first one. It still uses the LAG function and, for some reason, avoids using CTEs, which makes the query more cryptic and difficult to read. Thus, adding simply an instruction didn’t solve the problem.

SELECT
  CURRENT_DATE() AS run_date,
  DATE(purchase_date) AS purchase_date,
  product_marketing_category,
  gross_sales,
  gross_sales - LAG(gross_sales) OVER (
    PARTITION BY product_marketing_category, EXTRACT(MONTH FROM purchase_date), EXTRACT(DAY FROM purchase_date)
    ORDER BY purchase_date
  ) AS yoy_sales_change,
  SAFE_DIVIDE(
    gross_sales - LAG(gross_sales) OVER (
      PARTITION BY product_marketing_category, EXTRACT(MONTH FROM purchase_date), EXTRACT(DAY FROM purchase_date)
      ORDER BY purchase_date
    ),
    LAG(gross_sales) OVER (
      PARTITION BY product_marketing_category, EXTRACT(MONTH FROM purchase_date), EXTRACT(DAY FROM purchase_date)
      ORDER BY purchase_date
    )
  ) AS yoy_sales_change_pct
FROM (
  SELECT
    purchase_date,
    product_marketing_category,
    COALESCE(gross_sales, 0) AS gross_sales
  FROM
    sales_table
)
WHERE
  EXTRACT(YEAR FROM purchase_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE()) - 1 AND EXTRACT(YEAR FROM CURRENT_DATE())
ORDER BY
  purchase_date,
  product_marketing_category;

Adding a specific instruction to not use LAG functions

One of the proper ways to perform this calculation, is by using JOINs, this guarantees that the expected calculation (Year-over-year) is correctly performed for each day. Thus, the following instruction was added at the bottom of the prompt:

**Avoid Using the LAG Function**: The LAG function can lead to inaccurate \
results when there are missing values in the data. \
 Consider using alternative methods, such as JOINs that better handle gaps \
in the data set.

The generated query is again — wrong. Although it opted to not use LAG function, the query displays a series of flaws:

  • Incorrect DATE_SUB() logic: If you analyze closely, the SQL code is not really calculating anything — the YoY growth will always be 0, which doesn’t make any sense. This is because, in the previous_year_sales CTE, the SQL code uses DATE_SUB(purchase_date, INTERVAL 1 YEAR). When this is later LEFT JOINed in the final SELECT statement with EXTRACT(YEAR FROM p.previous_purchase_date) + 1, it merely creates a copy of the column
  • Flawed JOIN choice: The model decided to use a LEFT JOIN. A LEFT JOIN keeps all records from the left table and matches records from the right table where available. However, the more robust approach would be to use a FULL JOIN. This ensures that even if the current year sales data for a determined product_marketing_category is NULL but , the data for last year sales (if not NULL) would still show in the final select statement.
  • Division by 0: To top it off, the generated SQL code doesn’t use SAFE_DIVIDE(). Evidently, this is far from a robust way of performing such calculations. When I executed the query on my production database, I encountered the following error:
WITH daily_sales AS (
    SELECT
        purchase_date,
        product_marketing_category,
        SUM(gross_sales) AS daily_gross_sales
    FROM
        sales_table
    GROUP BY
        purchase_date,
        product_marketing_category
),
current_year_sales AS (
    SELECT
        purchase_date,
        product_marketing_category,
        daily_gross_sales,
        EXTRACT(YEAR FROM purchase_date) AS year,
        EXTRACT(MONTH FROM purchase_date) AS month,
        EXTRACT(DAY FROM purchase_date) AS day
    FROM
        daily_sales
),
previous_year_sales AS (
    SELECT
        DATE_SUB(purchase_date, INTERVAL 1 YEAR) AS previous_purchase_date,
        product_marketing_category,
        daily_gross_sales AS previous_daily_gross_sales
    FROM
        daily_sales
)
SELECT
    c.purchase_date,
    c.product_marketing_category,
    c.daily_gross_sales AS current_year_sales,
    p.previous_daily_gross_sales AS previous_year_sales,
    (c.daily_gross_sales - p.previous_daily_gross_sales) / p.previous_daily_gross_sales * 100 AS yoy_growth_percentage
FROM
    current_year_sales c
LEFT JOIN
    previous_year_sales p
ON
    c.product_marketing_category = p.product_marketing_category
    AND c.month = EXTRACT(MONTH FROM p.previous_purchase_date)
    AND c.day = EXTRACT(DAY FROM p.previous_purchase_date)
    AND c.year = EXTRACT(YEAR FROM p.previous_purchase_date) + 1
ORDER BY
    c.purchase_date,
    c.product_marketing_category;

Conclusions

ChatGPT-4o struggles significantly with generating SQL code to perform Year-over-Year (YoY) calculations, a basic sales KPI. Even with explicit instructions, the model fails to produce a functional query. However, there are other strategies to consider, such as using few-shot prompt engineering and RAG in order to steer the response towards the right path.

This was just a very simple example; in production databases, the model would likely struggle even more. Additionally, there are multiple other sales KPIs, such other period calculation (WoW, QoQ) and Year-to-Date (YTD) in which the LLM would probably struggle too. Hopefully, this sheds some light on the problem and sparks some ideas on you.

Please feel free to share with me if you managed to solve this!

References

[1] — Li, B., Luo, Y., Chai, C., Li, G., & Tang, N. (2024). The Dawn of Natural Language to SQL: Are We Fully Ready? http://arxiv.org/abs/2406.01265.