SQL analysis and visualization: DEA-C01 study notes and practice questions
What to know about SQL analysis and visualization for the DEA-C01 exam, from the Data Operations and Support domain (22% of the exam guide), then 2 free questions to test it. Part of the DEA-C01 study guide.
SQL questions show a business ask or a short query and ask which SQL gives the right answer or runs faster.
SQL you should read fluently
JOIN types: INNER keeps matches; LEFT keeps all left rows with nulls for missing matches; FULL keeps both.
GROUP BY with HAVING filters after aggregation; WHERE filters before.
Window functions:ROW_NUMBER(), RANK(), LAG()/LEAD() and running totals with SUM() OVER (PARTITION BY ... ORDER BY ...). ROW_NUMBER() = 1 per key is the standard way to deduplicate or pick the latest record.
CTEs (WITH ...) break complex queries into readable steps.
COALESCE replaces nulls; COUNT(col) ignores nulls while COUNT(*) doesn’t.
APPROX_COUNT_DISTINCT (or approx_distinct in Athena) is much faster on huge data when an estimate is fine.
UNION ALL keeps duplicates and is faster than UNION.
Nested and semi-structured data
Athena: UNNEST arrays, dot notation for structs, JSON functions.
Redshift: the SUPER type with PartiQL navigation and unnesting.
Performance habits
Filter early on partition or sort key columns.
Avoid functions on filtered columns that stop pruning.
Join large tables on distribution keys in Redshift; use materialized views for repeated aggregations.
Check plans with EXPLAIN.
Visualizing
Amazon Quick Sight builds dashboards from Athena, Redshift, S3 and others. SPICE is its in-memory engine for fast dashboards with scheduled refresh; direct query shows live data.
Row-level security limits what each viewer sees.
Natural-language questions are available through Quick Sight’s generative BI features.
Common traps
WHERE on an aggregate instead of HAVING.
INNER JOIN dropping rows that have no match when the ask was “all customers”.
DISTINCT on every column when the ask is “the latest record per key”.
A logistics broker tracks the number of parcels handled each day at every depot in an Amazon Redshift table, with one row per depot per day. Days with no parcels are stored with a count of zero.
The operations team wants a dashboard column that shows each depot's average daily volume over the current day and the six days before it, so that short spikes are smoothed out.
Which SQL expression produces the rolling average?
Choose one answer.
AA. AVG(parcels) OVER (PARTITION BY depot_id ORDER BY parcel_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), averaging every day since the first record.
BB. AVG(parcels) OVER (PARTITION BY depot_id, parcel_date), which averages only the rows that share the same depot and day.
CC. SELECT depot_id, AVG(parcels) FROM depot_volume GROUP BY depot_id, which returns one average per depot for the whole table.
DD. AVG(parcels) OVER (PARTITION BY depot_id ORDER BY parcel_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Show the answer and explanation
Answer: D
A. Wrong: This window averages every day from the start of the data up to the current row, which is a running average. It keeps growing over time and does not smooth the last seven days.
B. Wrong: This averages only the rows on the same day for each depot, so the value does not roll across days. The average would be the same as the daily value.
C. Wrong: A GROUP BY returns one average per depot for the whole table, not a value per day. The result has no rolling column for the dashboard.
D. Right: Correct. The window partitions rows by depot, orders them by date, and averages the current row with the six rows before it. Each depot's rolling average is calculated separately, which gives the seven-day view the team wants. Learn more: Window functions (opens in a new tab)
Explanation
A rolling average is a window function with a frame of the current row and the six rows before it, ordered by date within each depot. Because every depot has one row per day, six preceding rows cover the six previous days. Running averages, same-day averages and plain group averages do not produce the seven-day view.
A hospital group's finance dashboard in Amazon Quick Sight (formerly Amazon QuickSight) is used by about 500 managers. The data comes from an Amazon Redshift cluster, and the cluster's query queue is already busy during the morning reporting peak. Managers accept data that is up to two hours old.
The team wants fast dashboard loads and a steady load on the Redshift cluster, and it wants a predictable refresh schedule.
Which approach meets these requirements?
Choose one answer.
AA. Import the dataset into SPICE and schedule a refresh every two hours, so dashboards read the in-memory copy.
BB. Keep the dashboard on direct query, so that every manager's click runs a new query against the Redshift cluster.
CC. Export the dataset to CSV every morning, and load the CSV file into QuickSight by hand before the managers arrive.
DD. Give each manager their own Redshift user, so that the cluster can allocate queue slots to each manager fairly.
Show the answer and explanation
Answer: A
A. Right: Correct. SPICE is Quick Sight's in-memory engine, so managers' interactions do not query Redshift. A refresh scheduled every two hours matches the freshness the managers accept and puts a predictable load on the cluster. Learn more: Importing data into SPICE (opens in a new tab)
B. Wrong: Direct query sends every interaction to the warehouse, which adds load during the morning peak that the team wants to avoid. Response times also depend on the cluster's queue.
C. Wrong: Manual exports are error-prone and depend on a person being available each morning. The option does not give a scheduled, predictable refresh.
D. Wrong: More users increase the number of concurrent queries against the cluster, which worsens the morning peak. The option does not reduce the load.
Explanation
SPICE keeps a copy of the dataset in Quick Sight, so dashboards respond quickly and do not send each click to the warehouse. A scheduled refresh every two hours matches the managers' tolerance and moves the warehouse load to a fixed time. Direct query, manual imports and more users all keep the load on the busy cluster.
Want more on SQL analysis and visualization? The 5 full-length practice sets have 11 more SQL analysis and visualization questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →