Question

Write the query in SQL:

Sample output:

Table:

For customer and product, show the average sales before and after each month (e.g., for February, show average sales of January and March. For "before" January and "after" December, display NULL> The "YEAR" attribute is not considered for this query-for example, both January of 2007 and January of 2008 are considered January regardless of the year. 2. Report #2: CUSTOMER PRODUCT MONTH BEFORE AVG AFTER AVG Bloom 2434 325 Bread Milk 254 3 Sam day integer integer integer character (2) integer prod character varying (20) Pepsi Bread Pepsi Fruits Milk Soap Eggs Yogurt month state quant cust character varying (20) year 12 4232 4167 4404 4369 210 2549 559 17 1 Bloom 2 Knuth 3 Emily 4 Emily 5 Helen 6 Emily 7 Bloom 8 Bloom 2 2001 NY 2005 PA 2006 CT 2000 NJ 2006 CT 2002 CT 2000 NJ 2004 PA 23 2 30 25 4

Answer 1

Hi,

Below is the query for the problem.

Assumptions -

1. Assuming table name as Test.

select cust as customer,prod as product,month,qty as current,LAG(qty, 1,null) OVER (PARTITION BY cust,prod ORDER BY month) as before_avg,
LEAD(qty, 1,null) OVER (PARTITION BY cust,prod ORDER BY month) as after_avg
from(
select cust,prod,month,avg(quant) as qty from test group by cust,prod,month order by prod,month) k
order by prod,month

2. Created sample data as per the image given in question. Here is the output of the query-