Deep dive: how analysts can use AI to augment their job

How can analysts use AI to augment their job?

The use of Artificial Intelligence (AI) has been revolutionizing the way we process and analyze data. One application of AI that is gaining popularity is the use of AI to write SQL queries. This technology can save significant amounts of time and increase efficiency for business analysts. In this article, we will discuss some of the ways AI can be used to write, edit, and enhance SQL queries, and provide some concrete examples of how this can be done.

The main avenue though which AI becomes useful for data analysis and SQL queries is through natural language processing (NLP). NLP is a field of study that focuses on the interaction between human language and computers. NLP can be used to interpret user queries written in natural language and convert them to SQL queries. This can save time for developers who may not be familiar with SQL syntax, or who need to generate complex queries quickly.

Using AI to help you query data

For example, imagine a business owner wants to know the total sales made in the last quarter. Instead of manually writing a SQL query to aggregate and sum the sales data, they can use an AI-powered natural language processing tool to generate the query by asking the AI: "Can you write a SQL query that calculate the total amount of sales made in the last quarter". The tool could interpret the request in natural language and generate a SQL query that looks something like this:

SELECT SUM(sales_amount) AS total_sales
FROM sales_table
WHERE sales_date >= '2022-10-01' AND sales_date <= '2022-12-31';

AI can write SQL queries is through automated query generation. AI algorithms can analyze data patterns and relationships and generate SQL queries automatically based on those patterns. This can be useful for tasks such as data exploration or ad hoc analysis, where developers may not have a specific query in mind.

For example, imagine an analyst wants to explore a new dataset that contains information about customer purchases. Instead of manually writing SQL queries to explore the data, they can use an AI-powered automated query generation tool to generate relevant queries by asking the AI: "Can you write a SQL query that shows the most popular products purchased". The tool could analyze the data and generate queries like:

SELECT product_name, COUNT(*) as purchase_count
FROM purchase_table
GROUP BY product_name
ORDER BY purchase_count DESC;

Or

SELECT customer_id, COUNT(DISTINCT purchase_date) as purchase_frequency
FROM purchase_table
GROUP BY customer_id
ORDER BY purchase_frequency DESC;

Using AI to improve data querying performance

In addition to generating queries, AI can be used to optimize existing queries for better performance. Query optimization is an important aspect of SQL query writing, as it can significantly impact query execution times and resource usage. AI algorithms can analyze existing SQL queries and suggest changes to the query structure or indexing to optimize performance and efficiency.

For example, consider a query that retrieves data from a large database table:

SELECT *
FROM large_table
WHERE column1 = 'value1' AND column2 = 'value2';

This query might take a long time to execute, as it has to search through the entire table to find the relevant rows. AI algorithms can analyze the table structure and suggest changes to the indexing or query structure to improve performance, for example:

SELECT *
FROM large_table
WHERE column1 = 'value1'
   AND EXISTS (SELECT 1 FROM large_table WHERE column2 = 'value2');

This revised query uses a subquery to eliminate rows that don't match the second condition, reducing the amount of data that needs to be searched.

Using AI to debug SQL queries

Debugging SQL queries can be a time-consuming and frustrating process for developers and data analysts. However, with the help of Artificial Intelligence (AI), identifying and resolving SQL query errors can be much more efficient.

Suppose we have a SQL query that is supposed to retrieve the names and email addresses of all customers who have placed orders in the last month. The initial query might look something like this:

SELECT Name, Email
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderDate >= '2022-01-01'

However, when we run this query, it doesn't return any results, even though we know there are customers who have placed orders in the last month. We suspect that there is an error in the query somewhere, but we're not sure where.

This is where AI can come in and help us debug the query. Using machine learning algorithms, the AI can analyze the query and identify potential errors. In this case, the AI might detect that the JOIN condition between the Customers and Orders tables is incorrect. Perhaps the CustomerID column is spelled incorrectly or the JOIN is using the wrong comparison operator.

After analyzing the query, the AI might suggest a revised query that looks like this:

SELECT Name, Email
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustID
WHERE OrderDate >= DATEADD(month, -1, GETDATE())

In this revised query, the JOIN condition has been corrected to use the correct column name for the Orders table, and the WHERE clause has been updated to retrieve orders from the last month using the built-in SQL Server function DATEADD.

With this revised query, we can run it and retrieve the names and email addresses of all customers who have placed orders in the last month.

Using AI to edit SQL queries

AI can also be used to edit SQL to query for something slightly different if business requirements change.

Suppose we have a SQL query that retrieves the top 10 products by sales in a given year. The initial query might look something like this:

SELECT TOP 10 ProductName, SUM(Quantity * UnitPrice) as Sales
FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
INNER JOIN Orders ON Orders.OrderID = OrderDetails.OrderID
WHERE YEAR(Orders.OrderDate) = 2022
GROUP BY ProductName
ORDER BY Sales DESC;

However, after running the query, we realize that we actually want to see the top 20 products by sales, instead of just the top 10. We could manually edit the query ourselves to change the "TOP 10" to "TOP 20", but there may be other ways to optimize the query for this new requirement.

This is where AI can come in and help us edit the query. Using machine learning algorithms, the AI can analyze the query and suggest edits that can improve its performance and efficiency. In this case, the AI might suggest a revised query that looks like this:

SELECT TOP 20 ProductName, SUM(Quantity * UnitPrice) as Sales
FROM (
   SELECT Products.ProductName, OrderDetails.Quantity, OrderDetails.UnitPrice, Orders.OrderDate
   FROM Products
   INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
   INNER JOIN Orders ON Orders.OrderID = OrderDetails.OrderID
   WHERE YEAR(Orders.OrderDate) = 2022
) as subquery
GROUP BY ProductName
ORDER BY Sales DESC;

In this revised query, instead of using the "TOP" clause to limit the results, the AI has suggested creating a subquery that first retrieves all the relevant data for the given year, and then groups and orders the data to retrieve the top 20 products by sales. This can help to optimize the query's performance and efficiency, and make it easier to modify in the future if the requirements change again. With this revised query, we can run it and retrieve the top 20 products by sales in the given year.

Ask AI to explain SQL for learning purposes

Finally, you can use AI for learning purposes. For example you can ask AI to generate an example SQL query and explain what it does.

SELECT customer_name, COUNT(order_id) as total_orders
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY customer_name
ORDER BY total_orders DESC;

Here's the explanation for this query that the AI returned:

This query retrieves the total number of orders made by each customer in the year 2022, and orders the results in descending order by the number of orders. Let's break down what each line of the query is doing:

• The SELECT statement specifies the columns we want to retrieve from the database. In this case, we want to retrieve the customer_name column and a count of the number of order_id values associated with each customer.
• The FROM statement specifies the tables we want to retrieve data from. In this case, we want to retrieve data from the customers and orders tables.
• The LEFT JOIN statement joins the customers and orders tables based on their customer_id values. This allows us to associate each order with the customer who placed it.
• The WHERE statement specifies the conditions we want to filter the data by. In this case, we want to retrieve only the orders made between January 1, 2022 and December 31, 2022.
• The GROUP BY statement groups the results by the customer_name column. This allows us to aggregate the data by customer and retrieve the count of orders for each customer.
• The ORDER BY statement orders the results by the total number of orders in descending order. This allows us to see which customers placed the most orders in 2022.

By running this SQL query, we can retrieve a list of customers and their total number of orders placed in the year 2022, ordered by the customers with the highest number of orders. This can be useful for analyzing customer behavior and identifying top customers for targeted marketing or other business strategies.

Conclusion

In conclusion, AI can be a powerful tool for learning, writing, editing and debugging SQL queries. It can automate the generation and optimization of queries. With the help of NLP, automated query generation, and query optimization, AI can significantly reduce the time and effort required to write SQL queries. As AI technologies continue to advance, we can expect to see even more innovative applications of AI in data analysis and query writing, helping augment data analysts' roles to allow them to work more productively and efficiently.

LogicLoop is exploring leveraging the power of AI to enable analysts to more efficiently query their data. Click here if you're interested in trying the AI + SQL beta.

‍