RahulSinghPundir/SQL_Wizard

1{
2    "fewshots_examples": [
3        {
4            "input": "How can we identify AEs who are not assigned to any works? ",
5            "query": "SELECT `name` FROM `aes` WHERE `id` NOT IN (SELECT DISTINCT `AE_code` FROM `ae_work`);"
6        },
7        {
8            "input": "Get the Last Audit Log Entry for Each User",
9            "query": "SELECT `audit_logs`.`user_id`, MAX(`audit_logs`.`created_at`) AS `last_audit_date`, `users`.`name` FROM `audit_logs` JOIN `users` ON `audit_logs`.`user_id` = `users`.`id` GROUP BY `audit_logs`.`user_id` ORDER BY `last_audit_date` DESC;"
10        },
11        {
12            "input": "no of work which are currently active.",
13            "query": "SELECT COUNT(WORK_code) AS no_of_works FROM mispwd.work_dashboards WHERE work_dashboards.worktaken=1;"
14        },
15        {
16            "input": "total no of live works in PD Almora and CD Ranikhet",
17            "query": "SELECT COUNT(WORK_code) AS no_of_works FROM mispwd.work_dashboards WHERE ee_office_id in ( SELECT ee_offices.id FROM mispwd.ee_offices WHERE `name` in ('PD Almora','CD Ranikhet ')) and work_dashboards.worktaken=1;"
18        },
19        {
20            "input": "no of live works where sanction cost is more then or equal to 2 cr  .",
21            "query": "SELECT COUNT(WORK_code) AS no_of_works FROM mispwd.work_dashboards WHERE work_dashboards.worktaken=1 AND work_dashboards.SCOST>=200;"
22        },
23        {
24            "input": "list of work's name in nabard 28 yozana of EE office pd almora",
25            "query": "SELECT work_dashboards.`WORK_name` FROM mispwd.work_dashboards WHERE yozana_id = ( SELECT yozanas.id FROM mispwd.yozanas WHERE yozanas.`name` = 'NABARD 28') AND ee_office_id = ( SELECT ee_offices.id FROM mispwd.ee_offices WHERE ee_offices.`name` = 'PD Almora') ;"
26        },
27        {
28            "input": "no of live works in EE office CD ranikhet ",
29            "query": "SELECT COUNT(WORK_code) AS no_of_works FROM mispwd.work_dashboards WHERE ee_office_id = ( SELECT id  FROM mispwd.ee_offices WHERE name = 'CD Ranikhet') AND work_dashboards.worktaken = 1 ;"
30        },
31        {
32            "input": "WORK_code of last five work sanctioned after 15 March 2024.",
33            "query": "SELECT `WORK_code` FROM `work_details` WHERE `AA_DATE` >= '2024-03-15' ORDER BY `AA_DATE` DESC LIMIT 5"
34        },
35        {
36            "input": "total no of live works in PD Almora and CD Ranikhet",
37            "query": "SELECT COUNT(*) FROM `ee_offices` WHERE `name` IN ('PD Almora', 'CD Ranikhet') AND `is_exist` = 1"
38        },
39        {
40            "input": "total no of live works in PD Almora and CD Ranikhet.",
41            "query": "SELECT * FROM `ee_offices`;"
42        }
43    ]
44}