defog

/

sqlcoder

like 300

Seems to work for generating a lot of queries, however, fails for following and many more similar scenarios

CREATE TABLE employee (
ID int(10) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT '',
tel1 varchar(255) DEFAULT NULL,
tel2 varchar(255) DEFAULT NULL,
pass varchar(255) DEFAULT '',
company varchar(256) DEFAULT NULL,
employee_code int(11) NOT NULL DEFAULT '0',
joining_date date DEFAULT NULL,
designation varchar(255) DEFAULT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=211 DEFAULT CHARSET=latin1

And ran the following prompt
"for mysql database give me the number of employees joined in each designation on monthly basis for last 1 year "

Got the following response

SELECT employee.designation,
count(employee.id) AS number_of_employees
FROM employee
WHERE employee.joining_date >= (CURRENT_DATE - INTERVAL '1 year')
GROUP BY employee.designation
ORDER BY number_of_employees DESC;

Where as ChatGPT gives following, which seems more appropriate for the prompt

SELECT
DATE_FORMAT(joining_date, '%Y-%m') AS month_year,
designation,
COUNT(*) AS num_joined
FROM employee
WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY month_year, designation
ORDER BY month_year, num_joined DESC;

gpt-3.5-turbo-0613 gives me

SELECT MONTH(joining_date) AS month, designation, COUNT(*) AS num_employees
FROM employee
WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY MONTH(joining_date), designation