From the course: Text to SQL: Amazon Redshift Serverless for Generative SQL in Amazon Q

Computing a running total

From the course: Text to SQL: Amazon Redshift Serverless for Generative SQL in Amazon Q

Start my 1-month free trial Buy for my team

Computing a running total

“

- [Instructor] In this movie we want to revisit our data model again, and we want to ask questions of Amazon Q because we want to perform a running total using SQL, and so we want to, in particular, explore the data set higher_ed_employee_salaries and also extract information from QS world ranking, and we will navigate back to Amazon Redshift, but we want to provide as much detail and context so that Amazon Q can retrieve the right information for us, so let's go back to Amazon Redshift, so we have our Tables, and let's start off with a question, so, "Count the running total "of name that includes Potter, "and include name, earnings, "and job description "in the year 2022," so I'm providing as much detail as possible in my prompt, but I'm not telling Q exactly which table or which data set, but I'm specifying the columns that I want to extract and also adding a filter so the results set will be related to the year 2022, so take a few seconds for us to generate the response, so it took a few seconds for Q to retrieve the right information for us, so we will add this code to our notebook, and then we will generate the code, so we'll open up the slider because we want a result set which is greater than 100, and we can see that we have employees from this table, university_employee_salary, and their name includes, so I particularly wanted Potter, and we have a job_description and earnings and running total, so we want to improve the question because we specifically want to include the name Potter, so we can see that we have other names in the result set. "Please try again, "and only include "the name Potter." Okay, so hopefully, the result that we've just improved, that we've provided a follow-up question to allow Q to generate the more accurate responses for us, so we'll add this to our notebook. We'll open up the slider, and then we will generate this code again, so we can see that the result is much improved, and so Q was able to understand with more context as we follow it with more information because it's include Potter in a where clause in order to filter out the results, so we don't want any surnames with Pogan. We only want evidence of the presence of the name Potter for an employee, so it also provided information such as a job_description and earnings and a much more detailed select statement result set, so this is our running total, and we'll try another example with another question, and we'll ask Q, so, "Count the running total "of universities located in Asia, "and provide their name, rank, "and location code," and we'll see if Amazon Q will be able to understand. 'Kay, so it's taking a few seconds to generate the code, and we will add it to our notebook. We will open up this slider, and we will generate the code. 'Kay, so this is not quite the result that we want, so in particular, we wanted to have a look at the qs_world_ranking of universities that are only located in Asia, and it's given us a list of institutions which are located in the United States, United Kingdom, so let's improve the response by adding more information and follow up with more information for Amazon Q, so, "Please try again, "and only provide "the names of universities "based in Asia," and then we'll add this to our notebook. We'll open up this slider, and then we will generate this code again. Great, so it's provided institution name, and I want to improve the answer, and also, "Please provide the results "of the rank and location code," so we can add more details, provide more context for Amazon Q. 'Kay, so it's provided the rank, the institution, and also let's add this to our notebook, and let's try to run this code again. Great. This is much better. This is a much improved result, and so now, as we ask more questions, we're telling Q it keeps on learning, so it's provided the correct column, which is institution. It's also provided the rank display for qs_world_ranking, the location code of universities which are based in Asia.

Contents