Blog Post

A SQL Prompt AI Experiment with Window Clauses


SQL Prompt has an EAP with some AI capabilities. I was asked to do some testing, and while I’ve done relatively little, I did find some time to play with this recently and decided to document what happened.

I completed this and then clicked the < back arrow to return through previous versions of the code, which also shows what the prompt was that produced this code.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

My Experiment

I wanted to write a windowing function to test something. Instead, I decided to ask SQL Prompt to do this for me. I connected to a database, hit ALT+Z, and then entered this prompt in the popup. You can see the code produced.

2023-11-10 16_04_38-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

I only entered the prompt in the dialog. SQL Prompt wrote the highlighted code.

This wasn’t what I wanted, so I changed the prompt. You can see what I typed and what changed below.

2023-11-10 16_04_32-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

Not bad, but not what I wanted. Next I asked to move this to the window clause. I got a window clause, but it wasn’t used in the OVER() clause.

2023-11-10 16_04_21-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

Hmm, I tried again. Once I hit the play button, the AI does something and clears the prompt. That happened above, and this is the prompt I tried next:

2023-11-10 16_14_31-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

It still didn’t work. Actually, it really didn’t work. I’m glad I have a back button.

2023-11-10 16_14_42-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

Let me move on. I can fix that myself. Now I’ll change the column list.

2023-11-10 16_17_08-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

That worked:

2023-11-10 16_17_18-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

Can I add a second aggregate? I’ll try that.

2023-11-10 16_18_03-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

That didn’t work. It changed my SUM to the COUNT I wanted. Interesting.


This is interesting in that it can help me structure queries, but knowing how to prompt them matters. I do like the back and forth, as I hit back above and then added to my prompt, which worked.

2023-11-10 16_20_35-SQLQuery1.sql - ARISTOTLE.AdventureWorksLT2019 (ARISTOTLE_Steve (67))_ - Microso

This doesn’t feel faster to me, but I’ve written a lot of SQL code. I could structure this stuff quickly and easily. In fact, I’d likely use a * in the column list and try to build the window clauses I need first, then go back and add aggregates. Normal SQL Prompt would help me, and I think it would be faster than the AI.

However, I also know the AI is new, and there are possibilities here, which might be easier than me playing with syntax and looking through suggestions, even with SQL Prompt.

I’ll keep playing and hopefully I’ll find this useful. Or at least useful enough that I can give good feedback to the team experimenting here.

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating