An e-commerce analyst wants to compare each day’s sales to the previous day’s sales in order to spot drops in performance. Which SQL window function is best suited for this? OPTIONS:A. SUM OVER (PARTITION BY day)B. LAG with ORDER BY sale_dateC. LEAD with ORDER BY sale_dateD. ROW_NUMBER with PARTITION BY sale_date ANSWER:B EXPLANATION:LAG allows access to the previous row’s value when ordered by date, which is exactly what the analyst needs. LEAD looks forward (C), SUM aggregates without comparing rows (A), and ROW_NUMBER just assigns sequence numbers (D).

Treatment Protocols 4100, Base Hospital contact and reports are required in all the following situations, select all that apply:

A university is analyzing exam scores by department. Within the Math department, scores are ordered by exam date from oldest to newest. If the university uses LAST_VALUE(score), what result will be returned for Math? A) The lowest exam score in Math B) The average exam score in Math C) The most recent exam score in Math D) The highest exam score in Math Correct Answer: The most recent exam score in Math Explanation:Last value returns the value from the end of the ordered window. Since the window is ordered by date (oldest to newest), the final row corresponds to the most recent exam score. The lowest or highest score would only appear if the ordering were based on score rather than date. The average would require an aggregate function, not a window function.

A tech company tracks app download counts by country. In one week: USA: 5,000 downloads Brazil: 5,000 downloads Germany: 4,000 downloads If the company uses DENSE_RANK() with ORDER BY downloads DESC, what rank will Germany receive? A) 2 B) 3 C) 4 D) 5 Correct Answer: 2 Explanation:Dense rank assigns the same rank to ties but does not leave gaps. USA and Brazil both have rank 1 since they tied. The next distinct value (Germany with 4,000) becomes rank 2. A gap to rank 3 would only occur with RANK(), not DENSE_RANK().

A retail analyst wants to rank products by sales within each category and writes: SELECT category, product_name, SUM(sales) AS total_sales, ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) AS product_rank FROM sales GROUP BY category, product_name; What does product_rank show? OPTIONS:A. The overall rank of products across all categoriesB. The sequential ranking of products within each category, with no tiesC. The cumulative sales of products within each categoryD. The difference between the top product and the current product in each category ANSWER:B EXPLANATION:ROW_NUMBER() assigns a unique sequential rank within each category. Unlike RANK(), it doesn’t allow ties.

A logistics company wants to calculate each driver’s average delivery time compared to the overall company average. Why are window functions a good choice here? OPTIONS:A. They permanently store averages in the databaseB. They allow calculations across related rows without reducing the datasetC. They are faster than all other SQL approachesD. They eliminate the need for any joins ANSWER:B EXPLANATION:Window functions allow calculations across groups (like averages by driver or company-wide) while keeping all rows visible. They don’t permanently store values (A), don’t always guarantee faster performance (C), and don’t eliminate the need for joins (D).

An HR analyst writes the following query to analyze salaries: SELECT department, employee_name, salary, SUM(salary) OVER (PARTITION BY department) AS dept_total FROM employees; What does the dept_total column represent? OPTIONS:A. The total salary across the entire companyB. The salary of the highest-paid employee in each departmentC. The total salary for the department shown on each rowD. The cumulative salary ordered by employee name ANSWER:C EXPLANATION:The SUM OVER (PARTITION BY department) calculates the total salary within each department, repeated on each employee’s row.

A sales manager wants to see how much each product contributes to total revenue: SELECT product_id, SUM(sales) AS product_sales, SUM(sales) OVER () AS total_sales FROM sales GROUP BY product_id; What does the total_sales column represent? OPTIONS:A. The total sales for each product onlyB. The total sales across all products, repeated for every rowC. The maximum sales of any productD. The running total of product sales in descending order ANSWER:B EXPLANATION:The empty OVER() applies the sum across the entire dataset, repeating the same total for each product.

What is red tide?

What is the group of fungi associated with the greatest number of human diseases?