bird_evaluate_only.py

import os

from benchmarks.evaluate import run_evaluation, print_evaluation_report

db_url = os.getenv("BENCHMARK_DB_URL")

# TODO: import predictions from file
predictions = {
  "9": "/* \u0412\u044b\u0431\u0440\u0430\u0442\u044c \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0448\u043a\u043e\u043b \u0441\u0440\u0435\u0434\u0438 \u0442\u0435\u0445, \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u0441\u0440\u0435\u0434\u043d\u0438\u0439 \u0431\u0430\u043b\u043b \u043f\u043e \u043c\u0430\u0442\u0435\u043c\u0430\u0442\u0438\u043a\u0435 \u043d\u0430 \u0442\u0435\u0441\u0442\u0435 SAT \u0432\u044b\u0448\u0435 560 \u0438 \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0444\u0438\u043d\u0430\u043d\u0441\u0438\u0440\u0443\u044e\u0442\u0441\u044f \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u0447\u0435\u0440\u0435\u0437 \u0447\u0430\u0440\u0442\u0435\u0440 */\nSELECT COUNT(DISTINCT schools.school)\nFROM schools\nJOIN satscores ON schools.school = satscores.sname\nWHERE satscores.avgscrmath > 560\nAND schools.fundingtype LIKE '%charter%'",
  "13": "```sql\nSELECT DISTINCT phone /* \u0422\u0435\u043b\u0435\u0444\u043e\u043d\u044b \u0448\u043a\u043e\u043b */\nFROM schools\nORDER BY numge1500 DESC\nLIMIT 3;\n```",
  "17": "```sql\nSELECT DISTINCT \"charternum\" /* \u0420\u0430\u043d\u0436\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0448\u043a\u043e\u043b \u043f\u043e \u0438\u0445 \u0441\u0440\u0435\u0434\u043d\u0435\u043c\u0443 \u0431\u0430\u043b\u043b\u0443 \u043f\u043e \u043f\u0438\u0441\u044c\u043c\u0443, \u0433\u0434\u0435 \u0431\u0430\u043b\u043b \u0431\u043e\u043b\u044c\u0448\u0435 499, \u043f\u043e\u043a\u0430\u0437\u044b\u0432\u0430\u044f \u0438\u0445 \u043d\u043e\u043c\u0435\u0440\u0430 \u0443\u0441\u0442\u0430\u0432\u043e\u0432 */\nFROM \"schools\"\nWHERE CAST(\"avgscrwrite\" AS INTEGER) > 499\nORDER BY \"avgscrwrite\" DESC;\n```",
  "325": "```sql\n/* \u041f\u043e\u0434\u0441\u0447\u0435\u0442 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u043a\u0430\u043d\u0446\u0435\u0440\u043e\u0433\u0435\u043d\u043d\u044b\u0445 \u043c\u043e\u043b\u0435\u043a\u0443\u043b, \u0441\u043e\u0441\u0442\u043e\u044f\u0449\u0438\u0445 \u0438\u0437 \u0430\u0437\u043e\u0442\u0430 */\nSELECT COUNT(DISTINCT m.molecule_id) \nFROM molecule m\nJOIN atom a ON m.molecule_id = a.molecule_id\nWHERE a.element LIKE '%\u0430\u0437\u043e\u0442%'\n```",
  "279": "```sql\nSELECT DISTINCT m.label /* \u041d\u0430\u0439\u0442\u0438 \u043c\u0435\u0442\u043a\u0443 \u0434\u043b\u044f \u0441\u0432\u044f\u0437\u0438 \u0441 ID TR001_10_11 */\nFROM molecule m\nJOIN bond b ON m.molecule_id = b.molecule_id\nWHERE b.bond_id = 'TR001_10_11'\n```",
  "276": "/* \u0412\u044b\u0432\u0435\u0441\u0442\u0438 atom_id \u043f\u0435\u0440\u0432\u044b\u0445 \u0438 \u0432\u0442\u043e\u0440\u044b\u0445 \u0430\u0442\u043e\u043c\u043e\u0432 \u043c\u043e\u043b\u0435\u043a\u0443\u043b \u0441 \u0442\u0440\u043e\u0439\u043d\u044b\u043c\u0438 \u0441\u0432\u044f\u0437\u044f\u043c\u0438 */\nSELECT DISTINCT atom.atom_id\nFROM atom\nJOIN (\n    SELECT molecule_id\n    FROM bond\n    WHERE bond_type = 'triple'\n) AS triple_bonds\nON atom.molecule_id = triple_bonds.molecule_id\nORDER BY atom.atom_id\nLIMIT 2;",
  "498": "```sql\n/* \u041d\u0430\u0439\u0442\u0438 \u0444\u0440\u0430\u043d\u0446\u0443\u0437\u0441\u043a\u043e\u0435 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0435 \u043d\u0430\u0431\u043e\u0440\u0430 \u043a\u0430\u0440\u0442, \u0432 \u043a\u043e\u0442\u043e\u0440\u043e\u043c \u0435\u0441\u0442\u044c \u043a\u0430\u0440\u0442\u0430 \"Tendo Ice Bridge\" */\nSELECT DISTINCT st.translation\nFROM set_translations AS st\nJOIN cards AS c ON st.setcode = c.setcode\nWHERE c.name LIKE '%Tendo Ice Bridge%'\nAND st.language = 'French';\n```",
  "417": "```sql\n/* \u0412\u044b\u0431\u0435\u0440\u0435\u043c \u043f\u0440\u043e\u0446\u0435\u043d\u0442 \u044f\u043f\u043e\u043d\u0441\u043a\u0438\u0445 \u043f\u0435\u0440\u0435\u0432\u0435\u0434\u0435\u043d\u043d\u044b\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f \u043d\u0430\u0431\u043e\u0440\u0430\u043c\u0438 \u0434\u043e\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0439 */\nSELECT (COUNT(DISTINCT sets.name) * 100.0) / COUNT(DISTINCT set_translations.setcode) AS percentage_expansion_sets\nFROM sets\nJOIN set_translations ON sets.code = set_translations.setcode\nWHERE set_translations.language LIKE '%\u042f\u043f\u043e\u043d\u0441\u043a\u0438\u0439%'\nAND sets.type LIKE '%Expansion%'\n```",
  "345": "/* \u0412\u044b\u0431\u0440\u0430\u0442\u044c \u0441\u0442\u0430\u0442\u0443\u0441\u044b \u043b\u0435\u0433\u0430\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0434\u043b\u044f \u043a\u0430\u0440\u0442 \u0442\u0438\u043f\u0430 \"\u0430\u0440\u0442\u0435\u0444\u0430\u043a\u0442\", \u0443 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043d\u0435\u0442 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 \u043b\u0438\u0446 \u043d\u0430 \u043e\u0434\u043d\u043e\u0439 \u043a\u0430\u0440\u0442\u0435 \u0434\u043b\u044f \u0444\u043e\u0440\u043c\u0430\u0442\u0430 \u0438\u0433\u0440\u044b Vintage */\nSELECT DISTINCT l.status\nFROM legalities l\nJOIN cards c ON l.uuid = c.uuid\nWHERE c.type LIKE '%\u0430\u0440\u0442\u0435\u0444\u0430\u043a\u0442%'\nAND c.layout NOT LIKE '%multiple faces%' \nAND l.format = 'Vintage';",
  "1533": "/* \u0412\u044b\u0431\u0440\u0430\u0442\u044c \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u0435\u043d\u0438\u0435 \u0442\u0435\u0445 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0437\u0430\u043f\u043b\u0430\u0442\u0438\u043b\u0438 \u0431\u043e\u043b\u0435\u0435 \u0447\u0435\u043c 29.00 \u0437\u0430 \u0435\u0434\u0438\u043d\u0438\u0446\u0443 \u043f\u0440\u043e\u0434\u0443\u043a\u0442\u0430 \u0441 \u0438\u0434\u0435\u043d\u0442\u0438\u0444\u0438\u043a\u0430\u0442\u043e\u0440\u043e\u043c 5 \u0432 \u0430\u0432\u0433\u0443\u0441\u0442\u0435 2012 \u0433\u043e\u0434\u0430 */\nSELECT DISTINCT y.consumption\nFROM yearmonth y\nJOIN transactions_1k t ON y.customerid = t.customerid\nJOIN products p ON t.productid = p.productid\nWHERE p.productid = 5\nAND t.price > 29.00\nAND t.date BETWEEN '2012-08-01' AND '2012-08-31'",
  "1480": "```sql\n/* Find the gas consumption peak month for SME customers in 2013 */\nSELECT yearmonth.date, SUM(yearmonth.consumption) AS total_consumption\nFROM yearmonth\nJOIN customers AS c ON yearmonth.customerid = c.customerid\nWHERE c.segment LIKE '%SME%'\nAND yearmonth.date LIKE '2013%'\nGROUP BY yearmonth.date\nORDER BY total_consumption DESC\nLIMIT 1;\n```",
  "1529": "/* \u0421\u0443\u043c\u043c\u0430, \u043f\u043e\u0442\u0440\u0430\u0447\u0435\u043d\u043d\u0430\u044f \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u043c \"38508\" \u043d\u0430 \u0437\u0430\u043f\u0440\u0430\u0432\u043a\u0430\u0445 */\nSELECT SUM(t.amount) \nFROM transactions_1k t\nWHERE t.customerid = 38508;\n\n/* \u0421\u0443\u043c\u043c\u0430, \u043f\u043e\u0442\u0440\u0430\u0447\u0435\u043d\u043d\u0430\u044f \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u043c \"38508\" \u0432 \u044f\u043d\u0432\u0430\u0440\u0435 2012 \u0433\u043e\u0434\u0430 */\nSELECT SUM(t.amount) \nFROM transactions_1k t\nWHERE t.customerid = 38508\nAND t.date BETWEEN '2012-01-01' AND '2012-01-31';",
  "1472": "```sql\n/* \u041d\u0430\u0439\u0442\u0438 \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u0441 \u043d\u0430\u0438\u043c\u0435\u043d\u044c\u0448\u0438\u043c \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u0435\u043d\u0438\u0435\u043c \u0432 LAM \u0432 2012 \u0433\u043e\u0434\u0443 */\nSELECT DISTINCT yearmonth.customerid\nFROM yearmonth\nWHERE yearmonth.date LIKE '2012-%'\n  AND yearmonth.consumption = (\n    SELECT MIN(yearmonth.consumption)\n    FROM yearmonth\n    WHERE yearmonth.date LIKE '2012-%'\n  )\n```"
}

report = run_evaluation(predictions, "./data/some_queries.json", db_url)

print_evaluation_report(report)