Dates

#5
by pcapazzi - opened

Hi,
I had a successful deployment of this and responses are quick. My first test was OK with a straightforward count of groups. My second step was seeing if it could ask some basic sales data questions and I noticed it was having trouble in dealing with dates.

#1
Given the database schema, here is the SQL query that [QUESTION]How much revenue was made for each month going back 6 months? We are crossing over a year[/QUESTION]
[SQL]

SELECT date_trunc('month', s.txn_date) AS MONTH, SUM(s.txn_amount) AS total_revenue FROM sales s WHERE date_part('year', s.txn_date) >= date_part('year', CURRENT_DATE - interval '6 months') GROUP BY MONTH ORDER BY MONTH;

That will skip revenue generated in 2024. My expectation is to simply have txn_date >= current_date - interval '6 months'

#2
Given the database schema, here is the SQL query that [QUESTION]How much revenue was made for each month for the previous 12 months not including the current month?[/QUESTION]
[SQL]

SELECT date_trunc('month', s.txn_date) AS MONTH, SUM(s.txn_amount) AS total_revenue FROM sales s WHERE date_part('year', s.txn_date) = date_part('year', CURRENT_DATE) - 1 AND date_part('month', s.txn_date) != date_part('month', CURRENT_DATE) GROUP BY MONTH ORDER BY MONTH;

That will also skip revenue for January 2024 and would exclude this month from last year.

Variables used:
user_question = "How much revenue was made for each month for the previous 12 months not including the current month?"

table_metadata_string_DDL_statements ="""

create table sales (
txn_id bigint,
txn_date date,
txn_amount decimal(8,2),
txn_brand text (3)
)

"""

Defog.ai org

Thanks for raising this issue! To help us debug, could you confirm that you’re using num_beams = 4? The model does struggle occasionally for date questions without beam search

Additionally, could you share your ddl statements here?

Thanks!

Sure. num_beams was not in the parameter list of the endpoint’s UI. I’ll add it to my parameters in Python and try again. In case it matters my top-k was 1.0, top-p was .1 and temp was .1 as well.

The schema I used was very small and was included at the end of the message above.

Here's another run with the following parameters:
output = query({
"inputs": prompt,
"parameters": {
"top_k": 1,
"top_p": 0.01,
"temperature": 0.1,
"max_new_tokens": 1000,
"num_beams": 5
}
})

Here's the prompt:

Task

Generate a SQL query to answer [QUESTION]How much revenue was made for each month for the previous 12 months not including the current month?[/QUESTION]

Database Schema

The query will run on a database with the following schema:

create table sales (
txn_id bigint,
txn_date date,
txn_amount decimal(8,2),
txn_brand text (3)
)

Answer

Given the database schema, here is the SQL query that [QUESTION]How much revenue was made for each month for the previous 12 months not including the current month?[/QUESTION]
[SQL]

[{'generated_text': " SELECT date_trunc('month', s.txn_date) AS MONTH, SUM(s.txn_amount) AS total_revenue FROM sales s WHERE date_part('year', s.txn_date) = date_part('year', CURRENT_DATE) - 1 AND date_part('month', s.txn_date) != date_part('month', CURRENT_DATE) GROUP BY MONTH ORDER BY MONTH;"}]

Defog.ai org

Ah, thanks very much for raising this! This is a blindspot in our training data for the 7b model, and it's consistently getting this wrong. We'll get a fix out in the next few days.

The 34b model seems to handle this fine – but the 7b bugs out with your second questions. Will push a patch as soon as we can!

Thanks. 34b is 4x the expense for me though. I’ll wait for an update and try some other SQL tests in the meantime.

@rishdotblog We were excited about this model and the accuracy with which it comes. But due to the same date issue we are not able to move from 34b to 7b. Could you please share an update.

The new 7b-2 model i working great but needed fine tuning the prompt , but we are also facing some hallucination specially date part , looking forward to the updated version

Sign up or log in to comment