SQL Query using 5 aggregate functions

Question

How do I query the following:

For each customer, product and month, count the number of sales transactions that were between the previous and the following month's average sales quantities. For January and December, display NULL or 0. Can only use: 5 aggregate functions (sum, count, avg, max & min)

This is the table reference:

create table sales
    (
        cust    varchar(20),
        prod    varchar(20),
        day     integer,
        month   integer,
        year    integer,
        state   char(2),
        quant   integer,
        date    date
    );

Schema:

Example of my Desired Result

I am stuck with the following codes. I'm having a hard time how to execute it.

SELECT cust, prod, month, COUNT(*) AS SALES_COUNT_BETWEEN_AVGS
FROM sales

I use MySQL. Please guide me thank you.

Answer 1

Maybe try a query like below

• the first part is to calculate averages using group by
• second part is to use to those averages in a JOIN twice for past month and future month
• third part is WHERE clause in which we compare data. Note we have used greatest and least functions to determine min and max between two values from past and next month

Query

WITH T AS 
(SELECT cust, prod, month, AVG(quant) AS avg_quantity
FROM sales
group by cust, prod, month
)


SELECT S.cust, S.prod, S.month, COUNT(1) AS Sales_count
FROM sales S
LEFT JOIN T T1
ON T1.cust=S.Cust AND 
T1.prod=S.Prod AND
T1.Month=S.Month-1
LEFT JOIN T T2
ON T2.cust=S.Cust AND 
T2.prod=S.Prod AND
T2.Month=S.Month+1
WHERE S.quant BETWEEN IFNULL(LEAST(T1.avg_quantity,T2.avg_quantity),0) AND IFNULL(GREATEST(T1.avg_quantity,T2.avg_quantity),0)