SQL queries you must know (with examples) — PART 3

Previously I published two parts in the series of SQL queries you must now. This tutorial will provide 14 solutions to basic and intermediate queries across an opened motorbike producer database. We will look at the key queries in the sales area.

First of all, let’s look at our data table. We have a list of vehicles with their VIN (Vehicle Identification Numbers). Each vehicle has price, number of units sold, sale date and costs. We can also see dealer id who sold a vehicle and employee id who worked from the company’s side. Based on these inputs, we will be able to extract sales-related queries. We will not use other tables or joins in this tutorial.

1. How much Sales?

To calculate the total sales from a SQL database, you would need to use the SUM() function to add up the sales amounts for all of the relevant records.

select sum(price_unit) as total_sales from sales; 

Below is our result

2. How much costs?

To find the sum of production costs and additional costs from a SQL database, you would need to use the SUM() function as well. To find those two dimensions you need to use comma as separator. Here is the query:

select sum(production_costs) as total_production_costs, 
sum(additional_costs) as total_additional_costs from sales;

The result will look like this

3. How many vehicles were sold?

To calculate how many vehicles were sold from a SQL database, you would need to count the number of records in the relevant table that represent vehicle sales. The COUNT() function is used to count the number of records in the table, and the AS keyword is used to give the resulting count a name of “items_sold”.

select count(no_units_sold) as items_sold from sales;

So, we sold 5 items according to our table

4. The average price of a vehicle

We use AVG function in SQL to find the average price. Here is our query:

select avg(price_unit) as average_motorrad_price from sales;

So, the average price for a motorbike is equal to 15 930 euro.

5. How many motorbikes were sold in leasing and how many in credit?

To calculate how many motorbikes were sold in leasing and how many were sold on credit, you would need to count the number of records in the relevant table that represent motorbike sales for each payment method.

The specific clause you would use would be WHERE IN and then GROUP BY, because you need to identify particular information.

select vin, vehicle_name, type_of_contract from sales 
where type_of_contract in ('leasing','credit') 
group by vin, vehicle_name, type_of_contract;

As you can see there are three vehicles with contract type leasing and credit.

6. What is our profit?

Since we have in our table two types of costs — main and additional costs we would need to calculate sum of sales and then subtract both costs.

select (sum(price_unit) - sum(production_costs) 
- sum(additional_costs)) as total_profit from sales;

The result would be like this

Let’s check the correctives. In question 1 we have calculated total sales, they were 79 650 euros, while the costs were (39 000 + 4 000 = 43 000 euros). 79650–43000 = 36650.

7. Sales growth

Sales growth is an important measure of a business’s performance and sales forecasting. To calculate sales growth in SQL, you would need to

• retrieve the sales data for two different time period
• calculate the percentage change in sales between those periods
• present that change as the sales growth rate

Here’s a query that you would use

select extract(year from order_date) as year, sum(price_unit) 
as total_sales from sales group by year order by year;

select year1.year as year1,
    year1.sales as sales1,
    year2.year as year2,
    year2.sales as sales2,
    year2.sales - year1.sales as sales_diff
    from (select date_part('year', order_date) as year, 
    sum(price_unit) as sales
    from sales
    where date_part('year', order_date) = 2020
    group by date_part('year', order_date)) year1
    join (select date_part('year', order_date) as year, 
    sum(price_unit) as sales
    from sales
    where date_part('year', order_date) = 2022
    group by date_part('year', order_date)) year2
    on
    year1.year < year2.year;

8. Min Sales

If you need to identify minimum sales you would use this query

select min(price_unit) from sales limit 1;

This is our result

9. Max Sales

The same idea would be used for maximum sales

select max(price_unit) from sales limit 1;

10. Employee who brought the most sales

To find an employee who brought the most sales in your company you would need to use the SUM() function to add up the sales amounts for all of the relevant records and ORDER BY total sales. You can also limit your results choosing 1.

select emp_id, sum(price_unit) as total_sales from sales 
group by emp_id order by total_sales desc limit 1;

11. All employee who sold 1 vehicle

To find an employee who sold one vehicle, you would need to use WHERE clause.

select emp_id,no_units_sold from sales where no_units_sold =1;

There were three employees who sold just 1 item.

11. Total costs of ordered vehicles from Jan 2020 till Jan 2022

To calculate the total sales from a SQL database, you would need to use the SUM() function to add up the costs amounts for all of the relevant records. You need to specify the dates with WHERE clause.

select sum(production_costs + additional_costs) as total_costs from sales 
where order_date between '01-01-2020' and '01-01-2022';

So, out total costs for this period of time is 43 000 euros.

12. Total sales by country (2 ways)

12.1 Partition by — by columns

A partition function in SQL is used to divide a large table or index into smaller, more manageable partitions based on a specific column or set of columns. This allows for more efficient data management and query optimization.

select sale_date, country, price_unit, sum(price_unit) 
over(partition by country) as total_sales from sales order by country, 
sale_date;

A list of countries with their total sales will be represented, but since the purpose of PARTITION function to show you every column separately you could get duplicates like in the example below. For some purposes it could be handy, in other cases you can use sub-query in select function.

12.2 Sub-query in Select — by countries

Subqueries can be used to retrieve data from one or more tables and then use that data to aggregate the information.

select sale_date,country,(select sum(price_unit) from sales 
where country = main.country) from sales as main group by country, sale_date;

So in this case you would get three countries — United States, Italy and France.

13. Percent of sales by country

Another very common task that you can face as a data analyst is to calculate percentage of total sales. You could calculate the indicators using PARTITION BY function.

select sale_date, country, price_unit, 100.0 * price_unit / 
sum(price_unit) over (partition by country) as percent_of_sales 
from sales order by country, sale_date;

So the results would be like this

Hope this information was useful for you.

Have a nice day.