Top SQL Queries for Data Analysis

Improving your data analysis skills is possible by mastering advanced SQL techniques, which can assist in deeper exploration of datasets leading to valuable insights. This article highlights seven advanced SQL queries that can be helpful for data analysis. Each query will be presented along with its results, syntax details and an explanation of how the database computes it.

Top SQL Queries for Data Analysis

1. Grouping Data by Time Period
For the 1st Example, let us scrutinize information found in the sales table. The said data set contains a unique identifier for every product, the date when the deal has transpired, and the overall value of the transaction.

A very common in analysis scenario required data like specific date periods, such as month or year.

SELECT EXTRACT(YEAR FROM date) AS year, EXTRACT(MONTH FROM date) AS month, SUM(amount) AS total_sales_amount FROM sales GROUP BY year, month ORDER BY year, month;

Output:

Using the EXTRACT() function, we retrieve the year and month from the date field in this query. The data is then grouped based on the new year and month fields, and the total sales for each group are calculated by employing the SUM() function.

2. Ranking Data
Ranking information is frequently needed for complex data analysis purposes. For instance, you may want to rank your merchandise based on their sales figures to identify which items generate the majority of the earnings, or you may need to rank your outlets based on their subpar sales figures to pinpoint which locations are performing poorly.
Utilizing functions such as RANK() or DENSE_RANK() as window functions can allocate rankings to rows based on the value of a particular column. To begin, we shall examine the RANK() function:
SELECT product_id, SUM(amount) AS total_sales_amount, RANK() OVER(ORDER BY SUM(amount) DESC) AS rank FROM sales GROUP BY id;
Output: