Blog

A digital marketing analyst wants to see the change in website visits from one day to the next: SELECT visit_date, visits, LAG(visits) OVER (ORDER BY visit_date) AS prev_day_visits FROM web_traffic; What does the prev_day_visits column represent? OPTIONS:A. The number of visits on the next dayB. The number of visits on the current dayC. The number of visits on the previous day, or NULL if none existsD. The running total of visits across all days ANSWER:C EXPLANATION:LAG retrieves the previous row’s value when ordered by date. For the first row, there is no prior day, so the result is NULL.

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 hospital tracks patient wait times in the emergency department. Times are partitioned by day of the week and ordered by arrival time. If the hospital uses NTH_VALUE(wait_time, 3), what does the returned value represent? A) The third-longest wait time on that day B) The average of the first three wait times on that day C) The wait time of the third patient to arrive on that day D) The last patient’s wait time on that day Correct Answer: The wait time of the third patient to arrive on that day Explanation:Nth value returns the specified position within the ordered window. Since the data is ordered by arrival time, the third value corresponds to the third patient who arrived that day. It does not mean the third-longest time, an average, or the last patient’s wait time.

A regional manager ranks stores by sales in each city: SELECT city, store_id, SUM(sales) AS total_sales, RANK() OVER (PARTITION BY city ORDER BY SUM(sales) DESC) AS store_rank FROM stores GROUP BY city, store_id; If two stores tie for first place in a city, what will the next store’s rank be? OPTIONS:A. 2B. 3C. 1D. It depends on the number of total stores ANSWER:B EXPLANATION:RANK() skips numbers after ties (e.g., 1, 1, 3). To avoid skipped ranks, DENSE_RANK() would be used instead.

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 the group of fungi associated with the greatest number of human diseases?

What does polyphyletic mean? Which group of eukaryotes is polyphyletic?

What source do microbiologists use to find biochemical characteristics of bacteria?

What was the treatment for Cora’s Creutzfeldt-Jakob disease in Chapter 1’s Clinical Focus?

Name one unique feature of fungi and one unique feature of helminths.