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\mathrm{.}4\mathrm{.}2$ returns the holiday$\_$name, date$\_$analyzed, dow, and day$\_$of$\_$week.

The query in $1\mathrm{.}4\mathrm{.}4$ returns the dow, day$\_$of$\_$week, and expected$\_$sales$\_$dollars.

The query in $1\mathrm{.}4\mathrm{.}5$ returns the sale$\_$date and actual$\_$sales$\_$dollars for all days of $2020,$ 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 $85\%$ or less.

Sort by date$\_$analyzed.

My final query is:

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$(\text{'}2020-01-01\text{'},\text{'}2020-12-31\text{'},\text{'}1$ day'::interval$)$ AS my$\_$date

INNER JOIN

holidays AS h

ON

my$\_$date::date BETWEEN h$.$holiday$\_$date $-$ INTERVAL $\text{'}7$ days' AND h$.$holiday$\_$date $+$ INTERVAL $\text{'}7$ days'

WHERE

my$\_$date::date BETWEEN $\text{'}2020-01-01\text{'}$ AND $\text{'}2020-12-31\text{'}$

$),$

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$(\text{'}2020-01-01\text{'},\text{'}2020-12-31\text{'},\text{'}1$ day'::interval$)$ AS my$\_$date

INNER JOIN

sales AS sa

ON

my$\_$date::date $=$ sa$.$sale$\_$date

WHERE

my$\_$date::date BETWEEN $\text{'}2020-01-01\text{'}$ AND $\text{'}2020-12-31\text{'}$

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$(\text{'}2020-01-01\text{'},\text{'}2020-12-31\text{'},\text{'}1$ day'::interval$)$ AS my$\_$date

Left Join

sales as sa

ON my$\_$date::date $=$ sa$.$sale$\_$date

Where my$\_$date::date BETWEEN $\text{'}2020-01-01\text{'}$ AND $\text{'}2020-12-31\text{'}$

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 $\text{'}2020-01-01\text{'}$ AND $\text{'}2020-12-31\text{'})$

Select $*$ From sales$\_$data

UNION

Select $*$ 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\mathrm{.}85$

Order By

a$.$date$\_$analyzed;

The output I am getting is $($showing few rows, not all$)$:

holiday$\_$name date$\_$analyzed day$\_$of$\_$week actual$\_$sales$\_$dollars expected$\_$sales$\_$dollars ratio$\_$actual$\_$expected

$0$ New Year's Day $2020-01-01$ Wednesday $13377664\mathrm{.}212083\mathrm{.}41$

$1$ New Year's Day $2020-01-07$ Tuesday $13455664\mathrm{.}152097\mathrm{.}52$

$2$ MLK Day $2020-01-14$ Tuesday $13260064\mathrm{.}152067\mathrm{.}03$

$3$ MLK Day $2020-01-17$ Friday $12709264\mathrm{.}061983\mathrm{.}95$

$4$ MLK Day $2020-01-18$ Saturday $13520464\mathrm{.}302102\mathrm{.}71$

$5$ MLK Day $2020-01-19$ Sunday $13036864\mathrm{.}132032\mathrm{.}87$

$6$ MLK Day $2020-01-20$ Monday $13074064\mathrm{.}262034\mathrm{.}55$

The output I should have is:

holiday$\_$name date$\_$analyzed day$\_$of$\_$week actual$\_$sales$\_$dollars expected$\_$sales$\_$dollars ratio$\_$actual$\_$expected

$0$ New Year's Day $2020-01-01$ Wednesday $1337762632560\mathrm{.}51$

$1$ MLK Day $2020-01-17$ Friday $1270922525220\mathrm{.}5$

$2$ MLK Day $2020-01-18$ Saturday $1352043734900\mathrm{.}36$

$3$ MLK Day $2020-01-19$ Sunday $1303683574820\mathrm{.}36$

$4$ MLK Day $2020-01-20$ Monday $1307402532250\mathrm{.}52$

$5$ President's Day $2020-02-14$ Friday $1334522525220\mathrm{.}53$

$6$ President's Day $2020-02-15$ Saturday $1320963734900\mathrm{.}35$

$7$ President's Day $2020-02-16$ Sunday $1321803574820\mathrm{.}37$

$8$ President's Day $2020-02-17$ Monday $1352282532250\mathrm{.}53$

$9$ Easter $2020-04-12$ Sunday $1361643574820\mathrm{.}38$

$10$ Mother's Day $2020-05-10$ Sunday $1344963574820\mathrm{.}38$

$11$ Memorial Day $2020-05-22$ Friday $1291322525220\mathrm{.}51$

$12$ Memorial Day $2020-05-23$ Saturday $1329003734900\mathrm{.}36$

$13$ Memorial Day $2020-05-24$ Sunday $1307643574820\mathrm{.}37$

$14$ Memorial Day $2020-05-25$ Monday $1326242532250\mathrm{.}52$

$15$ Father's Day $2020-06-21$ Sunday $1351203574820\mathrm{.}38$

$16$ Independence Day $2020-07-03$ Friday $1329602525220\mathrm{.}53$

$17$ Independence Day