Task 3.2: Analyze data by using AWS services

• AAVG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)

  Incorrect

  Seven preceding rows plus the current row is an 8-day window.

• BAVG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

  Correct

  The frame covers the current row and the 6 rows before it in date order, which is 7 days for each store, and a window function keeps every row.

• CAVG(revenue) OVER (PARTITION BY store_id), with no ORDER BY in the window

  Incorrect

  Without ORDER BY the frame is the whole partition, so every row gets the store's all-time average.

• DAVG(revenue) with GROUP BY store_id, sale_date in the same query

  Incorrect

  Grouping by store and day averages one row per group. It returns each day's own revenue, not a rolling value.

Rolling metrics are window functions with an ordered frame. ROWS BETWEEN n PRECEDING AND CURRENT ROW covers n + 1 rows.

• AA provisioned Redshift cluster bought with 1-year reserved nodes

  Incorrect

  Reserved nodes discount the hourly rate of nodes that run all the time. The team would pay for an idle cluster most of the week.

• BA provisioned Redshift cluster with concurrency scaling turned on

  Incorrect

  Concurrency scaling adds capacity for bursts of concurrent queries. The main cluster still has to be sized and paid for while idle.

• CAmazon Redshift Serverless

  Correct

  Redshift Serverless provisions and scales capacity automatically, and you pay only for the capacity you use.

• DA provisioned Redshift cluster with elastic resize before each query

  Incorrect

  Resizing is a manual capacity change. Someone still sizes the cluster, and it bills while it waits between queries.

Steady, predictable load favors provisioned capacity with reservations; occasional and unpredictable load favors serverless, where idle time costs no compute.

• AAmazon Athena for Apache Spark

  Correct

  Apache Spark on Athena is serverless: you submit Spark code from notebooks and Athena determines and scales the compute it needs.

• BAmazon EMR with JupyterHub

  Incorrect

  JupyterHub on EMR gives notebooks, but the team would choose, size and manage the cluster.

• CAmazon Athena SQL queries in the console query editor

  Incorrect

  The query editor runs SQL. It does not run PySpark code in notebooks.

• DAn AWS Glue DataBrew project on a sample of the data

  Incorrect

  DataBrew is a visual, no-code preparation tool. It does not run the team's PySpark code.

Athena offers two engines: SQL and Apache Spark. The Spark engine gives notebook users PySpark without any cluster to manage.

• Aarray_join(tags, ',') in the SELECT list

  Incorrect

  array_join converts the array into a single string, so each order still produces one row.

• BCROSS JOIN UNNEST(tags) AS t(tag)

  Correct

  CROSS JOIN with UNNEST flattens an array into multiple rows, one for each element.

• Ccardinality(tags) in the SELECT list

  Incorrect

  cardinality returns the length of the array, not its elements.

• Dtags[1] in the SELECT list

  Incorrect

  The [] operator returns one element, here the first, so the other tags are lost.

Counting by array element needs the array turned into rows first; UNNEST does that, and GROUP BY can then count per tag.

• ACONCAT(order_id, product_name) in the SELECT list, grouped by order_id

  Incorrect

  CONCAT joins two expressions within one row. It does not combine values from several rows of a group.

• BLISTAGG(product_name, ',') WITHIN GROUP (ORDER BY product_name)

  Correct

  For each group, LISTAGG orders the rows by the ORDER BY expression and concatenates the values into a single string.

• CCOUNT(DISTINCT product_name) with GROUP BY order_id

  Incorrect

  COUNT returns how many values there are, not the values themselves.

• DMAX(product_name) with GROUP BY order_id

  Incorrect

  MAX returns a single value from each group, so the other product names are lost.

String aggregation across rows is LISTAGG's job; WITHIN GROUP controls the order of the values in the result.

• Arevenue / NULLIF(visits, 0)

  Correct

  NULLIF returns null when its two arguments are equal, so a zero divisor becomes null and the division returns null instead of failing.

• Brevenue / COALESCE(visits, 0)

  Incorrect

  COALESCE returns the first non-null value; a visits value of 0 is not null, so the divisor is still 0.

• Crevenue / NVL(visits, 1)

  Incorrect

  NVL is the same as COALESCE and replaces only nulls, so a 0 divisor stays 0.

• DROUND(revenue / visits, 2)

  Incorrect

  ROUND rounds the result of the division, which still fails before rounding can happen.

NULLIF is the inverse of COALESCE: it turns a chosen value into null, which makes it the standard guard against dividing by zero.

• ATurn on query result reuse with a maximum age for the dashboard workgroup

  Incorrect

  Result reuse returns stored results when a query is run again. New or changed queries still compete for capacity.

• BSet a per-query data usage limit on the other teams' workgroups

  Incorrect

  Data usage limits cap how much data a query scans. They do not reserve capacity for the dashboards.

• CCreate a capacity reservation and assign the dashboard workgroup to it

  Correct

  Capacity reservations give dedicated serverless capacity, let you choose which workloads use it, and need no changes to SQL queries.

• DPartition the dashboard tables by date and filter on the partitions

  Incorrect

  Partitioning reduces the data each query scans, but it requires query changes and reserves no capacity.

When concurrency, not scan size, is the problem, Athena capacity reservations give a workload its own processing capacity.

• AA CTAS table built from the join

  Incorrect

  CTAS stores the query results as data files in S3, a copy that does not change when the source tables do.

• BA saved query that holds the join

  Incorrect

  Saved queries can be edited and run in the console, but analysts cannot select from them as a table.

• CA summary table filled by INSERT INTO each hour

  Incorrect

  INSERT INTO adds rows to a stored table, which is an extra copy that is up to an hour old.

• DAn Athena view defined by the join

  Correct

  A view is a logical table, not a physical one; its query runs each time the view is referenced, so it reflects current data.

Views give reusable logic without storage; CTAS and INSERT INTO give materialized copies when query speed matters more than freshness.