1 4 6 Find holiday days, and days within a one week of a holiday, where the actual sales are reduced by 1 5 or more from expected sales Now that we have some preliminary queries written, we can use them as subqueries to a larger query Remember to remove the order by clause when using them as subqueries The query in 1 4 2 returns the holiday name, date analyzed, dow, and day of week The query in 1 4 4 returns the dow, day of week, and expected sales dollars The query in 1 4 5 returns the sale date and actual sales dollars for all days of 2 0 2 0 , even days where AGM was closed Join the 3 subqueries together to return holiday name, date analyzed, day of week, actual sales dollars, and expected sales dollars Derive a column ratio actual expected by dividing actual sales dollars by the expected sales dollars, rounded as shown Filter where the ratio actual expected ( unrounded ) is 8 5 or less Sort by date analyzed My query is given below which used all three subqueries I get WRONG INCORRECT expected sales dollars data in the rows This causes the ratio to be incorrect as well Please check the joins and the overall query to find why the query gives incorrect expected sales dollars All subqueries work as expected individually Subquery a gives 1 7 2 rows when run by itself Subquery b gives 7 rows when run by itself Subquery c gives 3 6 6 rows when run by itself My query after using all the sub queries WITH a AS ( SELECT h description AS holiday name, my date date AS date analyzed, EXTRACT ( DOW FROM my date date ) AS dow, TO CHAR ( my date date, 'Day' ) AS day of week FROM generate series ( ' 2 0 2 0 0 1 0 1 ' , ' 2 0 2 0 1 2 3 1 ' , ' 1 day' interval ) AS my date INNER JOIN holidays AS h ON my date date BETWEEN h holiday date INTERVAL ' 7 days' AND h holiday date INTERVAL ' 7 days' WHERE my date date BETWEEN ' 2 0 2 0 0 1 0 1 ' AND ' 2 0 2 0 1 2 3 1 ' ) , b AS ( SELECT EXTRACT ( DOW FROM my date date ) AS dow, TO CHAR ( my date date, 'Day' ) AS day of week, ROUND ( AVG ( COALESCE ( sa total amount, 0 ) ) , 2 ) AS expected sales dollars FROM generate series ( ' 2 0 2 0 0 1 0 1 ' , ' 2 0 2 0 1 2 3 1 ' , ' 1 day' interval ) AS my date INNER JOIN sales AS sa ON my date date sa sale date WHERE my date date BETWEEN ' 2 0 2 0 0 1 0 1 ' AND ' 2 0 2 0 1 2 3 1 ' GROUP BY dow, day of week ) , c AS ( WITH sales data AS ( SELECT my date date AS sale date, COALESCE ( SUM ( sa total amount ) , 0 ) AS actual sales dollars FROM generate series ( ' 2 0 2 0 0 1 0 1 ' , ' 2 0 2 0 1 2 3 1 ' , ' 1 day' interval ) AS my date LEFT JOIN sales AS sa ON my date date sa sale date WHERE my date date BETWEEN ' 2 0 2 0 0 1 0 1 ' AND ' 2 0 2 0 1 2 3 1 ' GROUP BY my date date ) , closed days AS ( SELECT holiday date AS sale date, 0 AS sales dollars FROM holidays WHERE closed flag true AND holiday date BETWEEN ' 2 0 2 0 0 1 0 1 ' AND ' 2 0 2 0 1 2 3 1 ' ) SELECT sale date, actual sales dollars FROM sales data UNION SELECT sale date, sales dollars AS actual sales dollars FROM closed days ) SELECT a holiday name, a date analyzed, a day of week, c actual sales dollars, b expected sales dollars, ROUND ( ( c actual sales dollars b expected sales dollars ) , 2 ) AS ratio actual expected FROM a INNER JOIN c ON a date analyzed c sale date LEFT JOIN b ON a dow b dow AND a day of week b day of week WHERE ( c actual sales dollars b expected sales dollars ) 0 8 5 ORDER BY a date analyzed

Question

1   4   6 Find holiday days, and days within a one week of a holiday, where the actual sales are reduced by 1 5   or more from expected sales Now that we have some preliminary queries written, we can use them as subqueries to a larger query  Remember to remove the  order by   clause when using them as subqueries  The query in 1   4   2 returns the holiday   name, date   analyzed, dow, and day   of   week  The query in 1   4   4 returns the dow, day   of   week, and expected   sales   dollars  The query in 1   4   5 returns the sale   date and actual   sales   dollars for all days of 2 0 2 0 , even days where AGM was closed  Join the 3 subqueries together to return holiday   name, date   analyzed, day   of   week, actual   sales   dollars, and expected   sales   dollars  Derive a column ratio   actual   expected by dividing actual   sales   dollars by the expected   sales   dollars, rounded as shown  Filter where the ratio   actual   expected ( unrounded ) is 8 5   or less  Sort by date   analyzed  My query is given below which used all three subqueries  I get WRONG   INCORRECT expected   sales   dollars data in the rows  This causes the ratio to be incorrect as well  Please check the joins and the overall query to find why the query gives incorrect expected   sales   dollars  All subqueries work as expected individually  Subquery   a   gives 1 7 2 rows when run by itself  Subquery   b   gives 7 rows when run by itself  Subquery   c   gives 3 6 6 rows when run by itself  My query after using all the sub queries  WITH a AS ( SELECT h   description AS holiday   name, my   date  date AS date   analyzed, EXTRACT ( DOW FROM my   date  date ) AS dow, TO   CHAR ( my   date  date, 'Day' ) AS day   of   week FROM generate   series ( ' 2 0 2 0   0 1   0 1 ' , ' 2 0 2 0   1 2   3 1 ' , ' 1 day'  interval ) AS my   date INNER JOIN holidays AS h ON my   date  date BETWEEN h   holiday   date   INTERVAL ' 7 days' AND h   holiday   date   INTERVAL ' 7 days' WHERE my   date  date BETWEEN ' 2 0 2 0   0 1   0 1 ' AND ' 2 0 2 0   1 2   3 1 ' ) , b AS ( SELECT EXTRACT ( DOW FROM my   date  date ) AS dow, TO   CHAR ( my   date  date, 'Day' ) AS day   of   week, ROUND ( AVG ( COALESCE ( sa   total   amount, 0 ) ) , 2 ) AS expected   sales   dollars FROM generate   series ( ' 2 0 2 0   0 1   0 1 ' , ' 2 0 2 0   1 2   3 1 ' , ' 1 day'  interval ) AS my   date INNER JOIN sales AS sa ON my   date  date   sa   sale   date WHERE my   date  date BETWEEN ' 2 0 2 0   0 1   0 1 ' AND ' 2 0 2 0   1 2   3 1 ' GROUP BY dow, day   of   week ) , c AS ( WITH sales   data AS ( SELECT my   date  date AS sale   date, COALESCE ( SUM ( sa   total   amount ) , 0 ) AS actual   sales   dollars FROM generate   series ( ' 2 0 2 0   0 1   0 1 ' , ' 2 0 2 0   1 2   3 1 ' , ' 1 day'  interval ) AS my   date LEFT JOIN sales AS sa ON my   date  date   sa   sale   date WHERE my   date  date BETWEEN ' 2 0 2 0   0 1   0 1 ' AND ' 2 0 2 0   1 2   3 1 ' GROUP BY my   date  date ) , closed   days AS ( SELECT holiday   date AS sale   date, 0 AS sales   dollars FROM holidays WHERE closed   flag   true AND holiday   date BETWEEN ' 2 0 2 0   0 1   0 1 ' AND ' 2 0 2 0   1 2   3 1 ' ) SELECT sale   date, actual   sales   dollars FROM sales   data UNION SELECT sale   date, sales   dollars AS actual   sales   dollars FROM closed   days ) SELECT a   holiday   name, a   date   analyzed, a   day   of   week, c   actual   sales   dollars, b   expected   sales   dollars, ROUND ( ( c   actual   sales   dollars   b   expected   sales   dollars ) , 2 ) AS ratio   actual   expected FROM a INNER JOIN c ON a   date   analyzed   c   sale   date LEFT JOIN b ON a   dow   b   dow AND a   day   of   week   b   day   of   week WHERE ( c   actual   sales   dollars   b   expected   sales   dollars )     0   8 5 ORDER BY a   date   analyzed

Accepted Answer

The Answer is in the image, click to view ...

Question

1 . 4 . 6 Find holiday days, and days within a one week of a holiday, where the actual sales are reduced by 1

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Database Concepts

Students also viewed these Databases questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question