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

Questions 1–5

1. 1application · medium

   A data engineering team wants to add machine learning capabilities to their existing PostgreSQL data warehouse. They need an open-source solution that runs inside the database and supports regression, classification, and clustering. Which option should they select?

   • AA set of PostgreSQL window functions
   • BA PostgreSQL trigger that calls an external Python API
   • CMADlib
   • DAn ordered aggregate function
   Reveal Solution Discussion Select an answer first
2. 2expert · hard

   A retail company wants to segment customers using k-means clustering. The data includes customer_id, total_spend, and visit_count. They also need to rank customers by total_spend within each cluster after clustering. The clustering must run in-database. Which approach should they use?

   • AUse an ordered aggregate to assign cluster IDs, then use ROW_NUMBER() OVER (PARTITION BY cluster_id ORDER BY total_spend DESC)
   • BRun MADlib's k-means function to assign cluster IDs, then use RANK() OVER (PARTITION BY cluster_id ORDER BY total_spend DESC) to rank customers within clusters
   • CUse RANK() OVER (PARTITION BY total_spend ORDER BY visit_count) to assign clusters, then use MADlib's linear regression to rank
   • DExport data to an external tool for clustering, then import cluster IDs and use LAG() to rank
   Reveal Solution Discussion Select an answer first
4. 3application · medium

   A financial analyst needs to compare each transaction's amount to the amount of the next transaction for the same account, ordered by transaction date. Which window function should they use?

   • AROW_NUMBER() OVER (PARTITION BY account_id ORDER BY transaction_date)
   • BLEAD(amount) OVER (PARTITION BY account_id ORDER BY transaction_date)
   • CLAG(amount) OVER (PARTITION BY account_id ORDER BY transaction_date)
   • Dpercentile_disc(amount) WITHIN GROUP (ORDER BY transaction_date)
   Reveal Solution Discussion Select an answer first
5. 4foundation · easy

   Which MADlib function would you use to predict a continuous numeric outcome, such as house prices, based on several input features?

   • Amadlib.kmeans
   • Bmadlib.logregr_train
   • Cmadlib.svm
   • Dmadlib.linregr_train
   Reveal Solution Discussion Select an answer first
6. 5foundation · easy

   Which SQL clause is used to define the set of rows over which a window function operates?

   • AORDER BY
   • BHAVING
   • COVER()
   • DGROUP BY
   Reveal Solution Discussion Select an answer first