SHARE
TWEET

sdfsdfs

a guest Nov 16th, 2019 112 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. . Print top 10 users - user_id and their rank - in ascending order of their ranks where rank is given by
  3. the total compost trash disposed by them (total weight of compost trash thrown, regardless of trash
  4. type, into the compost bin). Partial credit will be given for printing the top 10 user_id without their rank
  5. */
  6.  
  7.  
  8. SELECT          user_id,
  9.                 RANK() OVER
  10.                     (ORDER BY sum(LoadObservation.weight) DESC
  11.                     ) user_id,
  12.                 sum(LoadObservation.weight)
  13. FROM LocationSensor, WasteBin, CompostBin, LocationObservation, LoadObservation
  14. WHERE WasteBin.waste_bin_id = CompostBin.waste_bin_id
  15.     AND LocationObservation.x = WasteBin.x
  16.     AND LocationObservation.y = WasteBin.y
  17.     AND LoadObservation.timestamp = LocationObservation.timestamp
  18.     AND LocationObservation.sensor_id = LocationSensor.sensor_id
  19. GROUP BY user_id
  20. ORDER BY user_id
  21. LIMIT 0,10;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top