Trending February 2024 # The Art Of Query Building: Data Problems To Sql Queries # Suggested March 2024 # Top 2 Popular

You are reading the article The Art Of Query Building: Data Problems To Sql Queries updated in February 2024 on the website Minhminhbmm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 The Art Of Query Building: Data Problems To Sql Queries

Introduction

Learning Objectives 

Understand how data flows through a SQL query and use this to solve data problems.

Transform data problems into SQL queries using a keyword-based approach.

Dos and Don’ts when it comes to SQL keywords.

Finally, we’ll go through an example of using the underlying approach.

This article was published as a part of the Data Science Blogathon.

Table of Contents TABLE: Where Is My Data?

First, I like to start by considering all the tables I need in the query. You can do this by considering all the fields that will be needed to get the desired result, and then we can find them. An important thing to note is that multiple tables may have the same field. For example, user data can be present in multiple tables with different levels of aggregations. Hence, knowing what grain you want to pull in the results is essential. When building the query, I want you to pick one table, go through the steps, and return to the table. Also, if any array fields are needed in the table, now is a good time to unpack them.

FROM table_name LEFT JOIN UNNEST(table_array) AS array WHERE: What I Don’t Want?

Now that you know where your data is coming from, it’s time to know what information you need and, more importantly, what you don’t need from the table. So if the table has a partition or if the query demands filtering a certain type of record, now is the time to use it. Also, I need you to look at all fields in a table and think about all possible ways to filter your data here. You should really push yourself to add more filters.

To put it simply, the lesser data your query sees, the better it performs and avoids mistakes. Further, we often skip obvious filters as they seem too trivial; for example, if you’ve filtered on the partition date, it might still have multiple dates, so look for other date fields and add the filter.

WHERE partition_field = "date_value" AND col1 = "xyz" AND col2 IS NOT NULL ... GROUP BY: What’s the Grain?

Before you SELECT anything, I’d recommend doing a GROUP BY.  This is because having this first will often constrain what you select in your query. You can no longer do a `SELECT *`, which rarely makes sense. This will also leave out duplicate records before anything, and trust me; you don’t want duplicates flowing through your query as it’s difficult to determine their origin later. This also forces you to perform aggregations.

You often don’t need a field but only the aggregated value. Having this out of the way is helpful so that the rest of the query sees lesser data. So I’d recommend having a GROUP BY in your query for every table; even if it’s not explicitly needed, it’s an excellent way to avoid duplicates and only pulls in relevant data for the query.

SELECT col1, col2 FROM table_name GROUP BY col1, col2 SELECT: What Do I Actually Want?

After doing all the work above, you can now think about what fields you’ll actually pull from the specific table. If you have followed the above steps, the scope of the fields has already been reduced to the fields that are needed for the specific results.

A `SELECT *` slows down your query and may lead to incorrect results, as you may end up with extra records. The only time you should do it is when you’re trying to do a preview of all the fields in a table. On the contrary, selecting fewer fields first and then adding them later when needed is also feasible.

CASE: Conditions

A case statement is SQL’s way of making IF-ELSE statements. These enable you to capture complex logic and show SQL’s real ability. In addition to using CASE statements for traditional applications, you should also use them to alter fields before selection. For example, if you’re not concerned about a field’s specific value but only want a discrete value like Y/N, this is the time to convert the field using CASE statements.

One thing to note here is always to have an ELSE condition that tells SQL what to do if none of your conditions are met. We’re often confident that we’ve covered all the scenarios in our CASE statement, but data always surprises us. Hence it’s better to have an ELSE condition to avoid unknown behavior.  Personally, I like to add `ELSE NULL` so I can see that data didn’t fall into any of my expected scenarios.

CASE WHEN col = "value" THEN "Y" ELSE "N" END AS new_col Aggregations (Level 1): The Math

In this article, we’ll be talking about aggregations twice. At first, you should only worry about aggregations at a single table level. These are usually math-based, like sum, average, max, and min, or count-based. One thing to note for counts is that in 99% of the cases, you’d want to do a `COUNT(DISTINCT field_name)` instead of a regular `COUNT(field_name)` as the latter gives you a record count with duplicates in the specific field. A useful strategy is combining aggregations and CASE statements to capture complex logic in an easy manner. For example, building a purchase_indicator using the total transaction amount as below.

ALIAS: Nicknames

This may seem trivial, but this step is important for readability and writing correct queries. This is because many times, you’ll be deep down in your query looking for a field and not know what it is called. Hence it’s essential to make these worthwhile. Also, rather than using aliases for aggregated or derived fields, it’s helpful to use them for renaming fields with long or funky names in the table. In this way, even though you cannot do anything to the actual table, you can still call it something easy to work with in your own query.

Now if the query you’re building only uses a single table, this is where you stop. However, in most cases, there’ll be more than one table, so you can read further.

CTE: Building Blocks

CTEs or Common Table Expressions can build a temporary table inside your query without creating a real table. These are most useful for compartmentalizing your SQL query. This helps you to think clearly as every element becomes a separate table that can be combined.

At this point, you should put together all the above steps and wrap it in a CTE as done below. These also help in making changes to the query; for example, if you’re trying to edit certain conditions on a table, you can directly go to the associated CTE and make the change, enabling your change to cascade to the rest of your query.

WITH table_cte AS ( SELECT col1, chúng tôi AS col2_alias, FROM table_name LEFT JOIN UNNEST(table_array) AS array WHERE col4 = "value" GROUP BY col1, array.col2 )

Now go back to TABLEs and repeat the steps above for any other tables in your query.

JOINs: Watch Out SELECT col1, col2 FROM cte1 AS c1 JOIN cte2 AS c2 ON chúng tôi = c2.col1 GROUP BY col1, col2 Aggregations (Level 2): More Math

Now is the time to combine the metrics in the final result by aggregating the JOIN results. Because these will make our final results, it’s useful to throw in things like final aliases and FORMAT that make sure the numbers are readable with the appropriate commas.

SELECT FORMAT("%'d", SUM(amount)) AS total_amount ORDER BY: Make it Pretty

Ordering the results should always be saved for the last, as this can’t go in any CTE or subquery. The only time this can be avoided is when your query will be a production query where results are used and not read by someone. Otherwise, adding an `ORDER BY` is helpful, even if not explicitly required, as it will make reading the results much more accessible. Also, you can use fields here and CASE statements to allow for custom ordering of results.

LIMIT: Make it Digestible

Finally, if the plan with the query is to export or use the results to drive another calculation, you can skip this. However, in other cases, having the LIMIT clause is a must, which will only return a certain number of records, making it easier for you and your SQL engine. If you forget this and your query is about to return a million rows, your query will fail even without errors.

LIMIT 100 Putting It All Together

So let’s use our newly gained skills as an example. If you need more examples of queries with data and stories, head to my blog here.

The problem: We have an e-commerce store, and the marketing team wants a report of users who’ve not made a purchase in the last month. The state should break this down the user is in and the last interaction they had on the website.

WITH user_demographics AS ( SELECT user_id, address.state AS state FROM demographics LEFT JOIN UNNEST(address) AS address WHERE country = "USA" GROUP BY user_id, address.state ), user_purchases AS ( SELECT user_id, FROM transactions GROUP BY user_id ), SELECT * EXCEPT(rnk) FROM ( SELECT user_id, event, RANK() OVER(PARITION BY user_id, event ORDER BY date DESC) AS rnk ) t WHERE chúng tôi = 1 ), user_no_pruchases AS ( SELECT a.* FROM user_demographics a LEFT JOIN user_purchases b ON a.user_id = b.user_id WHERE (b.user_id IS NULL OR agg_purchase = "N") ), user_no_purchase_events AS ( SELECT user_id, state, event USING(user_id) GROUP BY user_id, state, event ) SELECT state, event, COUNT(DISTINCT user_id) AS user_count FROM user_no_purchase_events GROUP BY state, event ORDER BY state, event LIMIT 100 Conclusion

Here’s what we learned today:

We started by visiting the importance of SQL and building queries to solve business problems.

Then we delved into a step-by-step approach that leverages SQL keywords to transform data problems into queries.

In this, we highlighted common mistakes that go along with SQL keywords, for example, not having an `ELSE NULL` in a CASE statement.

We also reviewed best practices when writing SQL queries, including `GROUP BY`, to prevent duplicates.

Finally, we discussed an approach to query building using CTEs to compartmentalize your query.

Following these steps, you can transform any business problem into a SQL query that yields desired results.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Related

You're reading The Art Of Query Building: Data Problems To Sql Queries

Role Of Leadership Towards Building A Data

Big data has become a catchphrase owing to its massive potential and exponentially increasing digitalization in all spheres of business. It is among the top priorities for organizations today. Virtually whatever we do today amounts to the generation of data that increasingly gets collected and analyzed. It is changing the way organizations do business. As much as data is important, it is necessary to harness this ocean of data in order to stay afloat. This requires prompt decision-making strategies and directional leadership to steer the businesses in the right direction. As digital revolution matures, data-driven strategies will become an increasingly crucial point of competitive differentiation. To bridge the gap between fast-changing technology magnified by human potential and human adaptability, organizations have to be flexible and fast-adapting. The faster the businesses adapt to this new culture, the sooner it will reap the benefits. In order to utilize the big data to its full potential, it is important that complementary efficient leadership roles are required. Efficient leadership can make way for a new and much required data-driven culture to fully adapt to this new digital transformation. The following list suggests some changes that organizations can undertake to move towards a data-driven culture.  

Change in Organizational Structure

With the increasing speed of digital transformation, it becomes imperative to shift continuously towards a better organizational design by discarding the previous one.  The top businesses today are the highest performing since they have adapted to the changes quickly. The system in which these businesses carry out their daily operations today is much different from how they used to function even ten years back. This continual adaptation has sustained them in the long run. Other organizations which were weighed down by age-old hierarchical practices have either lagged behind or perished. However, redesigning an organization is costly and risky, so, effective leadership and decision-makers are required in the forefront. It is also important to fully realize the nuances of the objective, requirements, and strategy to successfully implement these changes. Employees, especially in the human resources department should be updated of the new strategies in the organization they are in. Letting them

Investment of Resources

The businesses should invest time, money and other resources in building proper infrastructure, adopting new technologies and providing different kinds of leadership training to the employees. To create a more data-inclusive culture, hiring more people with technical skills is not always the solution. It is important for the employees to have the necessary soft-skills and to keep upgrading their technical skills as traditional skills may become redundant in this fast-paced transformation. In this new structure, communication tools will become very essential. Standardised training should be implemented to make communication a priority. To bring about this flexibility, re-designing the organizations with more investment of resources in these areas become necessary. The high-performing businesses have started both forward and backward integration for learning by securing tie-ups with online platforms, universities and other institutions to provide a more dynamic approach to training and learning. So reskilling the adjacent skills combined with leadership training and infrastructure will pave the way to a more data-driven culture. These new business models also need a new approach to leadership mindset and demand some specific skills like negotiation, resilience to lead the network of teams.  

Network of Teams

In this drive towards a data-oriented culture, there needs to be a hierarchical shift from traditional business operations. The modern view is that the companies should be viewed as a flexible network supported by well-balanced teams, empowered leaders and decision-makers. Successful organizations today are moving towards this team-oriented approach. A team-centric business model accounts for more flexibility and durability than the age-old hierarchical model with expertise in specific areas. Smaller and well-connected teams are easier to work with. The different network of teams can specialize in different areas of expertise and thus transparency in decision-making also improves. It also helps the businesses to stay nimble as teams can be formed and disbanded quickly and job roles and titles can be changed accordingly. High-performing teams should be analyzed regularly and other teams can be modeled after such teams. Moreover, there should be diversity in the teams to maintain team fluidity.  

Accountability and Data Exposure

With specific expertise teams, accountability also becomes more transparent. Individual, as well as team goals, should be set and shared throughout the network. This creates a sense of accountability and boosts productivity. The top business leaders must understand this new flexible and agile model and support processes to increase team fluidity so that transparency and efficiency increases. Data exposure and accessibility increases as more teams handle the data. This, in turn, helps the organizations to deal with the plethora of data and derive meaningful insights from them. So, this shift from isolated data platforms to multi-team access is necessary. This also helps the team to have more first-hand knowledge of the business processes. The teams dealing with data should be governed by management teams that should also be well-acquainted with data. Data should not be limited only to specific teams. There should be an entire network which deals, governs and manages data.  

Conclusion

Big data has become a catchphrase owing to its massive potential and exponentially increasing digitalization in all spheres of business. It is among the top priorities for organizations today. Virtually whatever we do today amounts to the generation of data that increasingly gets collected and analyzed. It is changing the way organizations do business. As much as data is important, it is necessary to harness this ocean of data in order to stay afloat. This requires prompt decision-making strategies and directional leadership to steer the businesses in the right direction. As digital revolution matures, data-driven strategies will become an increasingly crucial point of competitive differentiation. To bridge the gap between fast-changing technology magnified by human potential and human adaptability, organizations have to be flexible and fast-adapting. The faster the businesses adapt to this new culture, the sooner it will reap the benefits. In order to utilize the big data to its full potential, it is important that complementary efficient leadership roles are required. Efficient leadership can make way for a new and much required data-driven culture to fully adapt to this new digital transformation. The following list suggests some changes that organizations can undertake to move towards a data-driven chúng tôi the increasing speed of digital transformation, it becomes imperative to shift continuously towards a better organizational design by discarding the previous one. The top businesses today are the highest performing since they have adapted to the changes quickly. The system in which these businesses carry out their daily operations today is much different from how they used to function even ten years back. This continual adaptation has sustained them in the long run. Other organizations which were weighed down by age-old hierarchical practices have either lagged behind or perished. However, redesigning an organization is costly and risky, so, effective leadership and decision-makers are required in the forefront. It is also important to fully realize the nuances of the objective, requirements, and strategy to successfully implement these changes. Employees, especially in the human resources department should be updated of the new strategies in the organization they are in. Letting them get an HR degree online can help them get the right training on organizational development and hone their skills for the betterment of the chúng tôi businesses should invest time, money and other resources in building proper infrastructure, adopting new technologies and providing different kinds of leadership training to the employees. To create a more data-inclusive culture, hiring more people with technical skills is not always the solution. It is important for the employees to have the necessary soft-skills and to keep upgrading their technical skills as traditional skills may become redundant in this fast-paced transformation. In this new structure, communication tools will become very essential. Standardised training should be implemented to make communication a priority. To bring about this flexibility, re-designing the organizations with more investment of resources in these areas become necessary. The high-performing businesses have started both forward and backward integration for learning by securing tie-ups with online platforms, universities and other institutions to provide a more dynamic approach to training and learning. So reskilling the adjacent skills combined with leadership training and infrastructure will pave the way to a more data-driven culture. These new business models also need a new approach to leadership mindset and demand some specific skills like negotiation, resilience to lead the network of chúng tôi this drive towards a data-oriented culture, there needs to be a hierarchical shift from traditional business operations. The modern view is that the companies should be viewed as a flexible network supported by well-balanced teams, empowered leaders and decision-makers. Successful organizations today are moving towards this team-oriented approach. A team-centric business model accounts for more flexibility and durability than the age-old hierarchical model with expertise in specific areas. Smaller and well-connected teams are easier to work with. The different network of teams can specialize in different areas of expertise and thus transparency in decision-making also improves. It also helps the businesses to stay nimble as teams can be formed and disbanded quickly and job roles and titles can be changed accordingly. High-performing teams should be analyzed regularly and other teams can be modeled after such teams. Moreover, there should be diversity in the teams to maintain team chúng tôi specific expertise teams, accountability also becomes more transparent. Individual, as well as team goals, should be set and shared throughout the network. This creates a sense of accountability and boosts productivity. The top business leaders must understand this new flexible and agile model and support processes to increase team fluidity so that transparency and efficiency increases. Data exposure and accessibility increases as more teams handle the data. This, in turn, helps the organizations to deal with the plethora of data and derive meaningful insights from them. So, this shift from isolated data platforms to multi-team access is necessary. This also helps the team to have more first-hand knowledge of the business processes. The teams dealing with data should be governed by management teams that should also be well-acquainted with data. Data should not be limited only to specific teams. There should be an entire network which deals, governs and manages chúng tôi with these changes undertaken, the possibilities become endless as the companies can venture into new areas with more efficiency and security. The complete democratization of work will break the hierarchical order in favor of a power-balanced network of teams with more project-oriented approach. This, in turn, will keep the businesses ahead in the race.

Changing Data Source Location In Power Query

Loading a Single Excel Workbook

If you are loading data from a file with a query like this

and then move the file to another folder or change the filename, you just need to change the path/filename in the Source step.

In Excel In Power BI Desktop

Loading Files from a Folder

If you are loading multiple files from a folder on your PC the process is very similar.

You’ll have a query like this

In both of these cases you can also modify the query directly in the Advanced Editor by changing the file and/or folder path.

Moving Files Into the Cloud

Another common scenario is where you’ve created some queries with files on your PC and you then move the files either to OneDrive for Business or Sharepoint Online.

The changes to be made here are still fairly straight forward but because the files are being moved to the cloud, we also have to change the connector being used in the queries.

Moving a File to OneDrive for Business

We’ve already seen that the query to load this Excel file from my PC is

Looking at the query in the editor you can see that after the Source step, I end up with this 5 column table.

The transformations that begin with the Expanded Data step will be the same whether I’m loading the file from my PC or OneDrive. What I need to do is change the source loading step so that after loading the file from OneDrive, I end up with this same 5 column table. The transformations can then do their work as they have the same data (the 5 column table) to work on.

I’ve moved the file to OneDrive into a folder called Sales

I need to know how to access this file and I do this by looking in my browser’s address bar. In the image below I’ve highlighted in red the important part that I need. This is the first part of the URL I need to use in Power Query.

%2F is HTML code for a forward slash so the end of the URL can also be read as /Documents/Sales

I have everything I need now to access the file in Power Query. The full URL to the file is

All I need to do is change this line

to this

If you’re prompted to enter credentials to let Power Query know how to connect to OneDrive.

The process is similar to change location for a text/CSV file.

Loading Files from a Folder

I’m loading several Excel workbooks from my PC with this query

After the Source step I have this table – it’s 8 columns wide but I’ve chopped out the middle to make it fit the screen.

All transformation steps act on this table so after moving the files elsewhere, I need to create a new query that gives me the same table, so my transformation steps do not need to be altered.

I have just moved these files to one of our company Sharepoint sites. Our Sharepoint looks like this

I’ve moved the files into the MOTH Team Site, into a folder called World Domination. Perhaps the folder name gives away my secret scheme.

Enter the URL for the Sharepoint root

Sign in with your Microsoft or Organization account if required

You’ll then be presented with a list of all the files on your Sharepoint

Now inside the PQ editor, I only want the files in my World Domination folder

So filter the Folder path column to only include that folder

This gives me a table with the same structure as I had when loading these files from my PC.

Loading the files from Sharepoint and creating this table was done in 2 steps, loading the source and filtering the folder path.

What I need to do now is take these steps from this query and insert them into the query that does all my transformations.

I need to make one other change which is to modify the #”Removed Other Columns” step. Originally it was referencing the Source step, but because I had to filter out some rows when loading from Sharepoint, I’ve now got a #”Filtered Rows” step after the Source step.

Nothing else needs changing so the query can be saved and my files should now load from Sharepoint.

The Art Of Crafting Powerful Prompts: A Guide To Prompt Engineering

Introduction

Prompt engineering is a relatively new field focusing on creating and improving prompts for using language models (LLMs) effectively across various applications and research areas. We can easily understand the capabilities and restrictions of large language models( LLMs) with the aid of quick engineering skills. Researchers intensively use prompt engineering to increase LLMs’ capacity for various tasks, including question answering and mathematical reasoning. Developers use prompt engineering to develop dependable, effective prompting techniques that work with LLMs and other tools.

Learning Objectives

1. To understand the basics of Large Language Models (LLMs).

2. To design prompts for various tasks.

4. To create an Order Bot using the prompting techniques and OpenAI API.

This article was published as a part of the Data Science Blogathon.

Understanding Language Models What are LLMs?

A large language model (LLM) is a type of artificial intelligence (AI) algorithm that uses some techniques and a large set of data to understand, generate, summarize, and predict new content.

Language models use autoregression to generate text. The model forecasts the probability distribution of the following word in the sequence based on an initial prompt or context. The most likely word is then produced, repeated continuously to produce words based on the original context.

Why Prompt Engineering?

Although LLMs are great for generating the appropriate responses for various tasks, where the model predicts the probability distribution of the next word in the sequence and generates the most likely words. This process continues iteratively, and the given task is fulfilled. But then there are several challenges to generating the relevant responses.

Lack of common sense knowledge

Does not have the contextual understanding sometimes

Struggle to maintain a consistent logical flow

May not fully comprehend the underlying meaning of the text

To address these challenges, prompt engineering plays a crucial role. Developers can guide the language model’s output by carefully designing prompts and providing additional context, constraints, or instructions to steer the generation process. Prompt engineering helps mitigate language model limitations and improve the generated responses’ coherence, relevance, and quality.

Designing Prompts for Various Tasks

The first task is to load your OpenAI API key in the environment variable.

import openai import os import IPython from chúng tôi import OpenAI from dotenv import load_dotenv load_dotenv() # API configuration openai.api_key = os.getenv("OPENAI_API_KEY")

The ‘get_completion’ function generates a completion from a language model based on a given prompt using the specified model. We will be using GPT-3.5-turbo.

def get_completion(prompt, model="gpt-3.5-turbo"): model=model, messages=messages, temperature=0, # this is the degree of randomness of the model's output ) return response.choices[0].message["content"] Summarization

The process performed here is automatic text summarization, one of the common activities in natural language processing tasks. In the prompt, we just ask to summarize the document and enter a sample paragraph; one doesn’t give sample training examples. After activating the API, we will get the summarized format of the input paragraph.

text = """ Pandas is a popular open-source library in Python that provides high-performance data manipulation and analysis tools. Built on top of NumPy, Pandas introduces powerful data structures, namely Series (one-dimensional labeled arrays) and DataFrame (two-dimensional labeled data tables), which offer intuitive and efficient ways to work with structured data. With Pandas, data can be easily loaded, cleaned, transformed, and analyzed using a rich set of functions and methods. It provides functionalities for indexing, slicing, aggregating, joining, and filtering data, making it an indispensable tool for data scientists, analysts, and researchers working with tabular data in various domains. """ prompt = f""" Your task is to generate a short summary of the text Summarize the text below, delimited by triple backticks, in at most 30 words. Text: ```{text}``` """ response = get_completion(prompt) print(response)

Output

Question Answering

By providing a context with a question, we expect the model to predict the answer from the given context. So, the task here is an unstructured question answering.

prompt = """ You need to answer the question based on the context below. Keep the answer short and concise. Respond "Unsure about answer" if not sure about the answer. Context: Teplizumab traces its roots to a New Jersey drug company called Ortho Pharmaceutical. There, scientists generated an early version of the antibody, dubbed OKT3. Originally sourced from mice, the molecule was able to bind to the surface of T cells and limit their cell-killing potential. In 1986, it was approved to help prevent organ rejection after kidney transplants, making it the first therapeutic antibody allowed for human use. Question: What was OKT3 originally sourced from? Answer:""" response = get_completion(prompt) print(response)

Output

Text Classification

The task is to perform text classification. Given a text, the task is to predict the sentiment of the text, whether it is positive, negative, or neutral.

prompt = """Classify the text into neutral, negative or positive. Text: I think the food was bad. Sentiment:""" response = get_completion(prompt) print(response)

Output

Techniques for Effective Prompt Engineering

Effective, prompt engineering involves employing various techniques to optimize the output of language models.

Some techniques include:

Providing explicit instructions

Specifying the desired format using system messages to set the context

Using temperature control to adjust response randomness and iteratively refining prompts based on evaluation and user feedback.

Zero-shot Prompt

For zero-shot prompting, no examples are provided for training. The LLM understands the prompt and works accordingly.

prompt = """I went to the market and bought 10 apples. I gave 2 apples to the neighbor and 2 to the repairman. I then went and bought 5 more apples and ate 1. How many apples did I remain with? Let's think step by step.""" response = get_completion(prompt) print(response) Few Shot Prompts

When zero shot fails, practitioners utilize a few-shot prompt technique where they provide examples for the model to learn and perform accordingly. This approach enables in-context learning by incorporating examples directly within the prompt.

prompt = """The odd numbers in this group add up to an even number: 4, 8, 9, 15, 12, 2, 1. A: The answer is False. The odd numbers in this group add up to an even number: 17, 10, 19, 4, 8, 12, 24. A: The answer is True. The odd numbers in this group add up to an even number: 16, 11, 14, 4, 8, 13, 24. A: The answer is True. The odd numbers in this group add up to an even number: 17, 9, 10, 12, 13, 4, 2. A: The answer is False. The odd numbers in this group add up to an even number: 15, 32, 5, 13, 82, 7, 1. A:""" response = get_completion(prompt) print(response) Chain-of-Thought (CoT) Prompting

By teaching the model to consider the task when responding, make prompting better. Tasks that use reasoning can benefit from this. To achieve more desired results, combine with few-shot prompting.

prompt = """The odd numbers in this group add up to an even number: 4, 8, 9, 15, 12, 2, 1. A: Adding all the odd numbers (9, 15, 1) gives 25. The answer is False. The odd numbers in this group add up to an even number: 15, 32, 5, 13, 82, 7. A:""" response = get_completion(prompt) print(response)

Now that you have a basic idea of various prompting techniques let’s use the prompt engineering technique to create an order bot.

What All You Can Do With GPT?

The main purpose of using GPT-3 is for natural language generation. It supports lots of other tasks along with natural language generation. Some of these are:

Create an Order Bot

Now that you have a basic idea of various prompting techniques let’s use the prompt engineering technique to create an order bot using OpenAI’s API.

Defining the Functions

This function utilizes the OpenAI API to generate a complete response based on a list of messages. Use the parameter as temperature which is set to 0.

def get_completion_from_messages(messages, model="gpt-3.5-turbo", temperature=0): response = openai.ChatCompletion.create( model=model, messages=messages, temperature=temperature, # this is the degree of randomness of the model's output ) return response.choices[0].message["content"]

We will use the Panel library in Python to create a simple GUI. The collect_messages function in a Panel-based GUI collects user input, generates an assistant’s response using a language model, and updates the display with the conversation.

def collect_messages(_): prompt = inp.value_input inp.value = '' context.append({'role':'user', 'content':f"{prompt}"}) response = get_completion_from_messages(context) context.append({'role':'assistant', 'content':f"{response}"}) panels.append( pn.Row('User:', pn.pane.Markdown(prompt, width=600))) panels.append( pn.Row('Assistant:', pn.pane.Markdown(response, width=600, style={'background-color': '#F6F6F6'}))) return pn.Column(*panels) Providing Prompt as Context

The prompt is provided in the context variable, a list containing a dictionary. The dictionary contains information about the role and content of the system related to an automated service called OrderBot for a pizza restaurant. The content describes how OrderBot interacts with customers, collects orders, asks about pickup or delivery, summarizes orders, checks for additional items, etc.

import panel as pn # GUI pn.extension() panels = [] # collect display context = [ {'role':'system', 'content':""" You are OrderBot, an automated service to collect orders for a pizza restaurant. You first greet the customer, then collects the order, and then ask if it's a pickup or delivery. You wait to collect the entire order, then summarize it and check for a final time if the customer wants to add anything else. If it's a delivery, you ask for an address. Finally, you collect the payment Make sure to clarify all options, extras, and sizes to uniquely identify the item from the menu. You respond in a short, very conversational friendly style. The menu includes pepperoni pizza 12.95, 10.00, 7.00 cheese pizza 10.95, 9.25, 6.50 eggplant pizza 11.95, 9.75, 6.75 fries 4.50, 3.50 greek salad 7.25 Toppings: extra cheese 2.00, mushrooms 1.50 sausage 3.00 Canadian bacon 3.50 AI sauce 1.50 peppers 1.00 Drinks: coke 3.00, 2.00, 1.00 sprite 3.00, 2.00, 1.00 bottled water 5.00 """} ] Displaying the Basic Dashboard For the Bot inp = pn.widgets.TextInput(value="Hi", placeholder='Enter text here…') button_conversation = pn.widgets.Button(name="Chat!") interactive_conversation = pn.bind(collect_messages, button_conversation) dashboard = pn.Column( inp, pn.Row(button_conversation), pn.panel(interactive_conversation, loading_indicator=True, height=300), ) dashboard Output

On the basis of the given prompt, the bot behaves as an order bot for a Pizza Restaurant. You can see how powerful the prompt is and can easily create applications.

Conclusion

In conclusion, designing powerful prompts is a crucial aspect of prompt engineering for language models. Well-crafted prompts provide a starting point and context for generating text, influencing the output of language models. They play a significant role in guiding AI-generated content by setting expectations, providing instructions, and shaping the generated text’s style, tone, and purpose.

Effective prompts result in more focused, relevant, and desirable outputs, improving language models’ overall performance and user experience.

To create impactful prompts, it is essential to consider the desired outcome, provide clear instructions, incorporate relevant context, and iterate and refine the prompts based on feedback and evaluation.

Thus, mastering the art of prompt engineering empowers content creators to harness the full potential of language models and leverage AI technology, such as OpenAI’s API, to achieve their specific goals.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Frequently Asked Questions

Q1. What does a prompt engineer do?

A. A prompt engineer designs and develops prompt systems, which are algorithms used in natural language processing to generate human-like text responses based on given inputs.

Q2. Is prompt engineering the future?

A. Prompt engineering is a rapidly evolving field with great potential for the future of AI technology and language processing.

Q3. Can anyone learn prompt engineering?

A. Yes, anyone with a strong interest in AI and language processing can learn prompt engineering through online courses, tutorials, and hands-on practice

Q4. Does prompt engineering require coding?

A. Prompt engineering typically involves coding skills, as engineers need to write and modify algorithms, work with programming languages, and understand the technical aspects of NLP frameworks.

Related

Comparison Of Different Sql Clauses

This article was published as a part of the Data Science Blogathon.

Introduction to SQL Clauses

SQL clauses like HAVING and WHERE both serve to filter data based on a set of conditions. The difference between the functionality of HAVING and WHERE  as SQL clauses are generally asked for in SQL interview questions. In this article, we will explain the functionalities and differences between the HAVING and WHERE clauses using an example.

HAVING Clause 

SQL HAVING clause fetches the necessary records from the aggregated rows or groups on the basis of the given condition. It is generally used along with the GROUP BY clause and applies to the column operations. It operates on the aggregate functions such as ‘SUM’, ‘COUNT’, ‘MAX’, ‘MIN’, or ‘AVG’. We can use the HAVING clause only with SELECT statements. It cannot be used with UPDATE or DELETE statements. The syntax is as follows:

Syntax of the HAVING Clause with the SELECT statement:

FROM Table_Name

WHERE condition

GROUP BY column_1, column_N

HAVING condition 

ORDER BY column_1, column_2, column_N;

WHERE Clause

SQL WHERE clause fetches the necessary records from a single table or multiple tables that meet the given condition. The WHERE clause can function without the GROUP BY clause and can perform row operations. It is used with single row functions like character functions, general functions, case conversion functions, date functions, or number functions. We can use the WHERE clause with any SELECT, UPDATE, and DELETE statement. The syntax is as follows:

a) Syntax of the WHERE Clause with the SELECT statement:

SELECT column_1, column_2, column_3, column_N

FROM Table_Name

WHERE condition;

b) Syntax of WHERE Clause with the UPDATE statement:

UPDATE Table_Name

SET column_1=value_1, column_2=value_2, column_3=value_3, column_N=value_N

WHERE condition;

c) Syntax of WHERE Clause with the DELETE statement:

DELETE FROM Table_Name WHERE condition;

Examples of HAVING and WHERE Clause Functionality

In the following example, we will demonstrate the functionality of the HAVING and WHERE clause:

Let’s start by creating a database called Student:

Use the Student database:

Let’s create the Student_Score table with Student_ID as the primary key:

Student_ID INT NOT NULL PRIMARY KEY,

Student_Name varchar(20),

Gender varchar(20),

Math_Score INT,

Bio_Score INT);

Insert the values into the Employee_detail table, then use the SELECT command to view the contents:

VALUES(1001, ‘Tom Ford’, ‘Male’, 68, 90),

(1002, ‘Ananya Verma’, ‘Female’, 97, 86),

(1003, ‘Eva Jackson’, ‘Female’, 86, 72),

(1004, ‘John Smith’, ‘Male’, 65, 91),

(1005, ‘Tanvi Sharma’, ‘Female’, 89, 63),

(1006, ‘Lilly Mathew’, ‘Female’, 74, 82);

HAVING clause with SELECT statement:

Our goal is to find out the average Math_Score of students who are gender grouped and have an average Math_Score greater than 60 and arranged in descending order.

FROM Student_Score

GROUP BY  Gender

ORDER BY AVG(Math_Score) DESC;

WHERE clause with SELECT statement:

 FROM Student_Score

WHERE clause with an UPDATE statement:

For the student with Student_ID 1004, we want to update the Math_Score column to 65 and the Bio_Score column to 95.

SET Math_Score=70, Bio_Score=95

WHERE Student_ID=1004;

We can use the SELECT statement to view the updated Student_Score table:

WHERE clause with DELETE statement:

WHERE Gender=’Male’;

We can use the SELECT statement to view the filtered record:

Difference Between HAVING and WHERE Clause

  HAVING WHERE

1. to fetch necessary records from the aggregated rows or groups on the basis of the specified condition. WHERE  clause enables you to fetch necessary records from the table on the basis of the specified condition.

2. HAVING clause should be used along with the GROUP BY clause and is used after the GROUP BY clause. It is possible for the WHERE clause to function without the GROUP BY clause and with the GROUP BY Clause, it’s been used before the GROUP BY Clause.

3. HAVING Clause applies to the column operations. WHERE Clause applies to the row operations.

4. Aggregate functions can be used in the HAVING Clause Aggregate functions cannot be used in the WHERE Clause

5. HAVING Clause is also known as a post-filter since it selects rows after aggregate calculations have been carried out. conducted.

6. HAVING Clause can only be used with ‘SELECT’ statements, but not with ‘UPDATE’ or ‘DELETE’ statements. WHERE Clause can be used with the ‘SELECT’, ‘UPDATE’, and ‘DELETE’ statements.

7. HAVING clause can be used with multiple row functions, such as ‘SUM’, ‘COUNT’, ‘MAX’, ‘MIN’, or ‘AVG’.

WHERE Clause can be used with a single row function such as character, general, case conversion, date, or number functions such as UPPER, LOWER, REPLACE, etc.

Conclusion

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Related

Exploring The Spiritual Through Art

Exploring the Spiritual Through Art BUAG brings together painting, photography, sculpture, new media

An image from Benny Merris’ An Other Another series, which merges landscapes with the human form, is one of the works featured in Elements, Rudiments, and Principles, on view at the Boston University Art Gallery at the Stone Gallery. Photo courtesy of the artist

December is for many a spiritual time of the year. With Hanukkah nearly over and Christmas just weeks away, it is a time for reflection. Elements, Rudiments, and Principles addresses the various ways that visual artists convey the spiritual. The new exhibition on view at the Boston University Art Gallery at the Stone Gallery is deeply personal and intimate.

The show includes work from 13 artists working in a variety of mediums: photography, painting, and sculpture. Some pieces more readily evoke the spiritual than others. Benny Merris’ photographs merge landscapes with the human form, suggesting the delicate balance between the human and natural world.

Xylor Jane’s colorful oil, pencil, and ink on wood canvases reveal her fascination with numbers. These bold works emphasize the order and rationality that numbers impose. And then there are Peter Coffin’s Aura Portraits, C-prints mounted on plexiglass, so ephemeral that they hint at the act of something being created—some gaseous substance taking shape. Hanna Sandin’s wonderful mixed media sculpture Untitled, made of everyday objects that the artist has reassembled to evoke something resembling a constellation, suggests a firmament where everything is in its place.

Perhaps the most overtly spiritual piece in the show is Sister Corita Kent’s Green Up, a serigraph made in 1966. Best known for her graphic Rainbow Swash, painted on a gas tank visible from Rt. I-93 in Dorchester, the serigraph is a series of words arranged backwards, asking: “When was the last time you saw a miracle?” Viewers cannot help but be startled by Kent’s painting. The question draws the viewer into an immediate dialogue with the work, and it’s difficult to walk away without attempting to answer the question.

Elements, Rudiments and Principles is the result of an ongoing conversation between BUAG director and chief curator Kate McNamara and Dana Frankfort, a College of Fine Arts assistant professor of visual arts. “We were both looking at and thinking about the parameters and structures that artists create to enable the creation of a material, a feeling, an idea,” McNamara says.

Viewers may be a bit hard-pressed to find the spiritual in Brian Belott’s playful representations of cats, Cat Diptych 1 and Cat Diptych 2, or in the partitions Heather Rowe uses in her architectural sculptures, like The Self, which in McNamara’s words “break up the process of looking through formal lines and perfected angles.” But, she notes, “the word spiritual is implied in both a literal and abstract application, and speaks to process as much as it does to subject matter.” So while Rowe’s sculptures may be simple in terms of the materials she uses, they “create incredible viewing mechanisms and play with the cinematic effects as the viewer engages” with them, McNamara says. And as for Belott’s cats, she urges visitors to take a closer look: “Belott’s cat paintings recall the careful, ritual, and ephemeral mandalas of Hindu and Buddhist practices.”

The arrangement of objects in this show has been carefully thought-out. The goal, says McNamara, was to “create a kind of spirit, a dialogue.” Viewers are meant to enjoy each piece not only on its own merits, but for the way it works with the others in the exhibition.

Elements, Rudiments, and Principles is on view at the Boston University Art Gallery at the Stone Gallery, 855 Commonwealth Ave., through December 20. Gallery hours are Tuesday through Friday, 11 a.m. to 5 p.m., Saturday and Sunday, 1 to 5 p.m.; the exhibition is free and open to the public.

Explore Related Topics:

Update the detailed information about The Art Of Query Building: Data Problems To Sql Queries on the Minhminhbmm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!