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

Questions 21–22

1. 21application · medium

   A healthcare analytics team uses a PostgreSQL database for patient records. They want to run k-means clustering on patient demographics directly in the database, without exporting data. Which tool should they use?

   • AMADlib's k-means function, invoked via a SQL query
   • BA window function with PARTITION BY to group patients into clusters
   • CA PostgreSQL trigger that runs a Python script on each insert
   • DAn ordered aggregate with WITHIN GROUP to assign cluster labels
   Reveal Solution Discussion Select an answer first
2. 22application · medium

   A sales team wants to rank employees by total sales within each region, but they need the ranking to be dense (no gaps after ties). For example, if two employees tie for 1st, the next employee should be ranked 2nd, not 3rd. Which window function should they use?

   • ARANK() OVER (PARTITION BY region ORDER BY total_sales DESC)
   • BROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC)
   • CNTILE(4) OVER (PARTITION BY region ORDER BY total_sales DESC)
   • DDENSE_RANK() OVER (PARTITION BY region ORDER BY total_sales DESC)
   Reveal Solution Discussion Select an answer first