Describe Advanced SQL Methods: Window Functions, Ordered Aggregates, and MADlib DATA-SCIENCE-FOUNDATIONS Practice Questions (Page 2)

Questions 6–10

1. 6foundation · easy

   What is the defining characteristic of an ordered aggregate in SQL?

   • AIt performs its calculation on a set of rows in a specified order, using the WITHIN GROUP clause.
   • BIt always returns a value for each row in the result set.
   • CIt returns a single value for the entire table regardless of order.
   • DIt can only be used with GROUP BY to produce multiple summary rows.
   Reveal Solution Discussion Select an answer first
2. 7expert · hard

   A data analyst needs to compute a 7-day moving average of daily sales for each product. The sales table has product_id, sale_date, and daily_sales. The analyst also needs to handle days with no sales (missing dates) by treating them as 0. Which approach should they use?

   • AUse RANK() OVER (PARTITION BY product_id ORDER BY sale_date) and filter to the last 7 ranks
   • BGenerate a calendar of all dates, left join with sales, fill missing daily_sales with 0, then use AVG(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
   • CUse SUM(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and divide by 7
   • DUse AVG(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) directly on the sales table
   Reveal Solution Discussion Select an answer first
4. 8application · medium

   A bank's data science team needs to build a logistic regression model to predict loan default risk. The model must be trained and scored directly inside the PostgreSQL database where the loan data resides, avoiding data movement to a separate analytics cluster. Which approach should the team choose?

   • AUse MADlib's logistic regression function via a SQL call, e.g., SELECT madlib.logregr_train(...)
   • BUse an ordered aggregate with WITHIN GROUP to fit the logistic regression
   • CExport the data to CSV and train the model using a Python script on a separate server, then import predictions back
   • DUse a PostgreSQL window function to compute the logistic regression coefficients manually
   Reveal Solution Discussion Select an answer first
5. 9application · medium

   A logistics company tracks shipment events in a table with columns shipment_id, event_time, and status. Analysts need to compute, for each shipment, the time elapsed since the previous event. Which SQL function and clause combination should they use?

   • AROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY event_time)
   • BLEAD(event_time) OVER (PARTITION BY shipment_id ORDER BY event_time)
   • CLAG(event_time) OVER (PARTITION BY shipment_id ORDER BY event_time)
   • Dpercentile_cont(event_time) WITHIN GROUP (ORDER BY event_time)
   Reveal Solution Discussion Select an answer first
6. 10foundation · easy

   What is the primary role of MADlib in the context of SQL databases?

   • ATo provide a separate analytics engine that requires exporting data out of the database.
   • BTo extend SQL with functions for machine learning and statistical analysis that run in-database.
   • CTo replace the need for SQL queries by providing a graphical interface.
   • DTo optimize query performance by automatically indexing tables.
   Reveal Solution Discussion Select an answer first