Skip to main content

Using Gen AI to write a fairly simple SQL query

So I wanted to see if I could test the different Gen AI models that are out there and get them to write a relatively simple SQL query. Basically select against my table, as detailed in the prompts to Gen AI, and produce a list of the fastest 1000 times at an event (that takes place weekly) and provide the times and names of the athletes that ran said times. Note that although I say view a lot I mean query because what are views if not stored queries anyway and I am using this in my DB as a view. 

Winner: Copilot

The original view can be seen below: 


So it is a fairly simple view with some logic in it to through some spanners in the works. The question is with the table definition and some explanation can the Gen AI platforms recreate a working version of the above view? 

The initial Prompt: 

I can't find a good way to format and embed my whole chats with the AI tools so I will work with what I have. Here is my original prompt that I used to get a starting point. 



Gemini: 

Overall this chat isn't very long and the first attempt was pretty close, it just missed the floor function around the minutes column and some quotes so I asked it to add those. 

"Please add a floor function to the fastest_time / 60 AS mins column Also the cte needs the double quotes around theparkrunID and Name columns. "

And here is the Gemini view, which I tested and it produced the right results.

The Gemini View:

Here

Copilot:

Next up I tried CoPilot. Copilot gave me a working piece of SQL out of the date, I did have to ask to expand the comments at the top and to add minute / seconds to the output but I like the way it did it. Overall was very impressed with it. No problems at all.


Copilot View:



ChatGPT:

Literally identical story to CoPilot with a slightly different output. I got a working view first time but didn't have the additional columns that were useful and it didn't get the comments right at the top and when I asked it to add them I think it went overboard! Sorry for the formatting it won't let me fix it!

ChatGPT View:



Summary:

Gemini - Original Prompt and 1 correction and gave a working view. Provided me with some nice to haves that the others didn't.
Copilot - Original Prompt worked and minor additions asked for and gave a working view.
ChatGPT - as with CoPilot

Think that overall the winner is Copilot, the final solution it gave me worked and it put in nice comments. I personally would then go with Gemini, even though it require a correction to add the floor that the others didn't it gave the view most closely matching my original and got the comments right. This leaves ChatGPT in last. Honestly however the experience with all 3 was very similar and they all produced more or less what I wanted within a handful of prompts. I wonder how they will cope with more complex data models.



Comments

Popular posts from this blog

My Latest project using Gen AI

So recently parkrun removed all their stats and as a keen running who is trying to work their way up the top 100 of their local parkrun I wanted to get some of these stats back and have a bit of "fun" at the same time. So here is a little "ETL" process that I developed with the help of Gen AI.  The steps of my ETL:  Copy and paste data into Google Sheets template where an AI produced formula extracts URLS from the text and puts them into a new field. This effectively allows me to extract the parkrun athlete id, the primary key, and use it in my analysis. I also have a column to autofill the data I am processing.  Use an Gen AI generated Google Apps script to process it into a processed sheet, this allows me to build up a backlog of events (I had over 500 to process).  This is then queried using a Gen AI Google sheets query to extract key information and columns / format times etc. I then ingest the fully processed sheet into Keboola directly from Google Sheets. ...

Gen AI News - 12/03/2024

Google’s Beta AI Content Rewriting Tool : Google is testing an AI tool that finds and rewrites quality content. However, some critics argue that it may incentivize the production of AI-generated low-quality content 1 . The New York Times and OpenAI Controversy : A court filing alleges that The New York Times paid someone to hack OpenAI’s products using deceptive prompts. The situation raises questions about the ethical use of AI 1 . Optimizing GPTs for Online Visibility : Learn how to increase online visibility and click-through rates for your GPT models in the GPT Store and Google Search with six practical tips 1 . AI Democratizing SEO or Amplifying Incompetence? : Understand what AI can realistically do for SEO and manage expectations regarding results 1 . Google’s “Help Me Write” AI Assistant : Google has launched an AI writing assistant called “Help Me Write” for the Chrome browser. It suggests text based on website context 1 . Google’s Gemini: Laptop-Friendly Open Language Model :...

Zapier

As much as I have enjoyed using Keboola there are some connections that it doesn't have or that just haven't worked for one reason or another. I actually came across Zapier as a solution for bringing in e-mails from parkrun to load my results every week. Honestly I have not found it to be as robust as Keboola but that might just be me archiving my e-mails before it completes it 15 minute poll.  The second use case I am working on is the pulling in Strava data, for a fitness dashboard the fact it has a built in connector for Strava is great, though I am worried given the activities I do that I might reach the limit.  I won't go into details on how to set things up but you can setup 5 Zaps that can run for a combined 100 runs during a month for free.  In my data platform / solution I am using Zaps to load harder to get / automate data. It doesn't add much from a technical point of view as it is just signing into a few account to get the data into Google Sheets for downstr...