Welcome to Excel Jet Consult blog post. In this blog post, we’ll explore how to write SQL to identify products that were consistently sold in every quarter of the year 2017. Let’s get started.
With a simple SELECT * FROM factSalesTable, we fetched all the records in the table as seen below
To identify the best selling products in every quarter of 2017, we are going to write and execute the query below:
SELECT DISTINCT Products FROM factSalesTable WHERE Year = 2017 GROUP BY Products HAVING COUNT(DISTINCT DATEPART(QUARTER, Date)) = 4;
In the SELECT statement, we specify that we want to display the unique product names (aliased as “Products”). We indicated the source table as “factSalesTable,” WHERE Year = 2017 clause filters the data to only include transactions from the year 2017. We group the results by the “Products” column, essentially grouping sales data by product.
The HAVING COUNT(DISTINCT DATEPART(QUARTER, Date)) = 4 clause filters the grouped results based on specified condition.
Finally, we use the DATEPART function to extract the quarter from the “Date” column for each transaction.
COUNT(DISTINCT DATEPART(QUARTER, Date)) calculates the number of distinct quarters in which each product was sold.
The condition = 4 ensures that only products sold in all four quarters are included in the result set.
From the screenshot below, we have 5 Products that were sold throughout all the quarters in 2017
From the above query, we have been able to establish products that consistently contribute to revenue. Hence, there is a need to give special and deserved attention to this products to sustain revenue in the long run