Data Analytics Fundamentals — Proficiency Exam

15 questions · pass with 50%

1. A director asks you to "pull all the sales data so we can have a look." What should you do first?
Export every table in the warehouse and start pivotingBuild a dashboard with last year's colour themeAsk what decision the numbers are meant to change, then write a measurable questionAverage every numeric column and present the means
2. Which of the following is a usable metric definition?
Median minutes from check-in to first clinician, for completed visits on weekdays, last 12 full weeksPatient experience, improvedQuality, highEngagement across the funnel
3. Why write a disconfirming test before you analyse?
So you can hide results that disagree with the sponsorBecause every analysis needs a p-valueTo justify pulling every column in the databaseTo stop yourself from hunting only for evidence that supports the favourite story
4. You receive a monthly CSV of order lines. What is the first structural move in Excel?
Make a pie chart of the first numeric columnConvert the block to a named Table, then summarise from that TableDelete unused columns so the file feels smallerCopy totals from last month's email into the bottom row
5. A PivotTable with Region on Rows and Sum of Revenue in Values is doing which job?
Joining two workbooks on a fuzzy name matchTraining a forecast modelReplacing the need to define a metricGrouping rows by region and aggregating revenue — the same idea as a grouped summary query
6. Why prefer XLOOKUP that returns a clear not-found over a formula that treats a miss as zero?
A silent zero can be summed as if the product existed and had no sales, which hides missing keysXLOOKUP cannot return numbers, only textNot-found values make the file smallerVLOOKUP is banned in every organisation
7. You need revenue by region for completed orders in 2025. Which clause defines the population of rows?
SELECT, because it lists region and revenueGROUP BY, because it names regionWHERE, because it keeps completed 2025 orders before groupingORDER BY, because it sorts the output
8. A query groups by week and also lists a customer's full name in SELECT without aggregating it. Why is that a problem?
Week is not a valid grouping key in SQLA week contains many customers, so the name is not unique in the groupSELECT cannot show text columnsNames must always be encrypted in SELECT
9. You want unique customers per channel, not the number of order lines. Which idea is correct?
Count distinct customer IDs inside each channel groupAverage the customer ID columnSelect every column and delete duplicates by eyeUse WHERE to keep only the first row of the table
10. Orders left-joined to products show 400 orders with a blank category. What does that mean?
The join type is wrong because left join never shows blanksThose orders had no matching product key on the right; left join kept the orders anywayRevenue for those orders is always zeroYou must delete those orders before presenting
11. An orders table has 5,000 rows. After joining to a "unique" product list, you have 7,200 rows. What should you suspect?
Duplicate product keys on the lookup caused fan-out, so some orders repeatedInner join always adds extra rows by designSQL COUNT is slower than Excel, so it double-countsYou should SUM first and never count rows
12. You have daily sales and a monthly target. You join on month, then SUM both sales and target. Why is the target total wrong?
Targets cannot be stored in databasesInner join deletes targetsEach month's target is repeated on every daily row, so summing target counts it many timesYou should have used a right join only
13. A revenue column contains numbers plus the text "pending." The SUM of the column ignores those cells or errors. What should you do first?
Replace pending with zero so the chart is smoothDelete the columnAverage the rest and call it revenueProfile how often pending appears, then write a rule: exclude from SUM and track pending as its own count
14. Two rows share the same order ID and line number but different quantities. What is the right instinct?
Always keep the larger quantityInvestigate which grain or restatement rule applies before deleting either rowAverage the two quantitiesKeep both so revenue is higher
15. Why keep the raw extract untouched?
Raw files cannot be stored legallyExcel cannot open the same file twiceSo you can rerun or change cleaning rules without guessing what was typed overBecause stakeholders never ask about old numbers

Answer all 15 questions to submit.