1 answer

Write the query in SQL: Sample output: Table: For customer and product, show the average sales before and after each

Question:

Write the query in SQL:

For customer and product, show the average sales before and after each month (e.g., for February, show average sales of Janua

Sample output:

Report #2: CUSTOMER PRODUCT MONTH BEFORE AVG AFTER AVG Bloom 2434 325 Bread Milk <NULL> 254 3 Sam

Table:

day integer integer integer character (2) integer prod character varying (20) Pepsi Bread Pepsi Fruits Milk Soap Eggs Yogurt

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

Answers

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-

.

Similar Solved Questions

1 answer
Required information [The following information applies to the questions displayed below.) Clearview Window Company manufactures windows...
Required information [The following information applies to the questions displayed below.) Clearview Window Company manufactures windows for the home-building industry. The window frames are produced in the Frame Division. The frames are then transferred to the Glass Division, where the glass and ha...
1 answer
Analyze the data. Investigators discovered a new member of the Bel family. To determine the function...
Analyze the data. Investigators discovered a new member of the Bel family. To determine the function of this new Bcl protein, the investigators used cell free extracts. They measured the levels of apoptosis in the cells by measuring the activation of caspase 9 in the cells and determined the effect ...
1 answer
Why is it necessary to standardize a indophenol solution? Why is it necessary to titrate blank...
Why is it necessary to standardize a indophenol solution? Why is it necessary to titrate blank samples? Why might the Vitamin C content be underestimated in the case of a heat processed juice sample?...
1 answer
Find the dy/dt for each pair of functions y=x^2-5x , x=t^2+3
Find the dy/dt for each pair of functions y=x^2-5x , x=t^2+3...
1 answer
Using suitable diagrams, explain the relationship between computer science and software engineering and discuss the three...
Using suitable diagrams, explain the relationship between computer science and software engineering and discuss the three ways of considering quality with practical examples....
1 answer
What is #pH# of a #0.002*mol*L^-1# solution of #HCl(aq)#?
What is #pH# of a #0.002*mol*L^-1# solution of #HCl(aq)#?...
1 answer
PARRICANED From 1020 HR STEEL W17H A DIAMETER -A CYLINDRICAL PRESSURE UESSEL, SHOWN BELOW, IS EMPLOYED...
PARRICANED From 1020 HR STEEL W17H A DIAMETER -A CYLINDRICAL PRESSURE UESSEL, SHOWN BELOW, IS EMPLOYED AS A WATER STORAGE TANK FOR A SMALL MUNICIPALITY, DETERMINE THE HEIGHT OF THE WATER "h" THAT CAN BE MAINTAINED IN THE TANK IF THE City's ENGINEERING DIRECTOR SPECIFIES A Safety FACTOR O...
1 answer
2. (25 points) A vehicle is moving at a speed of 70 mi/h on I-16 between...
2. (25 points) A vehicle is moving at a speed of 70 mi/h on I-16 between Savannah and Statesboro. The driver in the vehicle found that a crash occurred and wrecked cars blocked the oad ahead. Determine: a) the distance traveled by the vehicle before the driver push the brake; b) the distance travele...
1 answer
12,21,13,32,27,23,34,19,34,23,36,39 estion 16 12 points A direct-mapped cache has 4 blocks and each block holds four...
12,21,13,32,27,23,34,19,34,23,36,39 estion 16 12 points A direct-mapped cache has 4 blocks and each block holds four bytes of data. The memory system is byte addressable Determine if each of the memory references below is a hit H) or miss (Ml. You assume the cach Reference 12 21 13 32 27 23...
1 answer
A spacecraft with mass 1500 kg is in a circular orbit at an altitude 200 km...
A spacecraft with mass 1500 kg is in a circular orbit at an altitude 200 km above the surface of Earth. A) Use the Law of Universal Gravitation and Newton's 2nd law for circular motion to derive and find the speed of the spacecraft in this orbit. B) How much mechanical energy does the spacecraft...
1 answer
0.35 g of Nonyne gas was exposed to a temperature of 29 degrees C and a volume of 5.7 gallons. How much pressure does this exert?
0.35 g of Nonyne gas was exposed to a temperature of 29 degrees C and a volume of 5.7 gallons. How much pressure does this exert?...
1 answer
Question 1 A central theme from this week is that development is influenced by biological, psychological,...
Question 1 A central theme from this week is that development is influenced by biological, psychological, sociological, cognitive, and emotional factors. Question 1 options: True False Question 2 Which developmental theory emphasizes the importance of the people who influence our lives thr...
1 answer
Please include the graph! Economics 302: Intermediate Microeconomics II Assignment 3 (Due in my office 3:00,...
Please include the graph! Economics 302: Intermediate Microeconomics II Assignment 3 (Due in my office 3:00, Thursday March 19) 1. Consider two firms, 1 and 2, each producing an identical good simultaneously. This good market demand given by the (inverse demand function p = 10 - Y, where p is price,...
1 answer
Using the figure as a guide, for a mating experiment between Hfr KL98 and F- auxotrophic...
Using the figure as a guide, for a mating experiment between Hfr KL98 and F- auxotrophic recipient, a defined medium lacking which amino acid would use to select for recipients? a. histidine b. threoanine c. arginine d. proline e. tryptophan isdR thr eu azi lon proa Pura PYTB lac Isx purd argE malk ...
1 answer
Pseudocode please Special Problem for Swap A local zoo wants to keep track of how many...
Pseudocode please Special Problem for Swap A local zoo wants to keep track of how many pounds of food each of its three monkeys eats each day during a typical week. Write a program that stores this information in a two-dimensional 3 x 7 array, where each row represents a different monkey and each...
1 answer
What is included in the warning when the police arrest an individual (Miranda)
what is included in the warning when the police arrest an individual (Miranda)...
1 answer
Problem 6: 10 points Assume that observable is a random variable W = min X, <i<5...
Problem 6: 10 points Assume that observable is a random variable W = min X, <i<5 where {X; : 1<i<5} are independent and uniformly distributed on the unit interval (0 < x < 1). 1. Derive CDF for W, that is F(w) = PW <w]. 2. Evaluate density function, f(w) and identify it. 3. Find...