Skip to content

Instantly share code, notes, and snippets.

@rubensayshi

rubensayshi/1) total 2012-07-22

Created July 23, 2012 13:24
Show Gist options
  • Save rubensayshi/3163580 to your computer and use it in GitHub Desktop.
Save rubensayshi/3163580 to your computer and use it in GitHub Desktop.
Download ZIP
Listings
Raw
1) total 2012-07-22
mysql> SELECT listing_date, SUM(unit_price * quantity) / SUM(quantity) FROM (SELECT * FROM listing WHERE item_id = 1140 AND listing_date = '2012-07-22' ORDER BY id DESC) as listing GROUP BY listing_date;
+--------------+--------------------------------------------+
| listing_date | SUM(unit_price * quantity) / SUM(quantity) |
+--------------+--------------------------------------------+
| 2012-07-22 | 21.4561 |
+--------------+--------------------------------------------+
Raw
2) by poll time 2012-07-22
mysql> SELECT listing_date, listing_time, SUM(unit_price * quantity) / SUM(quantity) FROM (SELECT * FROM listing WHERE item_id = 1140 AND listing_date = '2012-07-22' ORDER BY id DESC) as listing GROUP BY listing_date, listing_time;
+--------------+--------------+--------------------------------------------+
| listing_date | listing_time | SUM(unit_price * quantity) / SUM(quantity) |
+--------------+--------------+--------------------------------------------+
| 2012-07-22 | 01:38:30 | 18.8571 |
| 2012-07-22 | 05:38:19 | 18.9243 |
| 2012-07-22 | 09:42:27 | 20.1228 |
| 2012-07-22 | 13:46:43 | 26.4857 |
| 2012-07-22 | 17:51:59 | 23.9185 |
| 2012-07-22 | 21:58:45 | 26.3853 |
+--------------+--------------+--------------------------------------------+
6 rows in set (0.00 sec)
Raw
3) all listings 2012-07-22
mysql> SELECT * FROM (SELECT * FROM listing WHERE item_id = 1140 AND listing_date = '2012-07-22' ORDER BY id DESC) as listing;+--------+--------------+--------------+---------+----------+------------+----------+
| id | listing_date | listing_time | item_id | listings | unit_price | quantity |
+--------+--------------+--------------+---------+----------+------------+----------+
| 237690 | 2012-07-22 | 21:58:45 | 1140 | 13 | 40 | 187 |
| 237689 | 2012-07-22 | 21:58:45 | 1140 | 2 | 39 | 66 |
| 237688 | 2012-07-22 | 21:58:45 | 1140 | 3 | 38 | 12 |
| 237687 | 2012-07-22 | 21:58:45 | 1140 | 14 | 37 | 207 |
| 237686 | 2012-07-22 | 21:58:45 | 1140 | 1 | 36 | 10 |
| 237685 | 2012-07-22 | 21:58:45 | 1140 | 5 | 35 | 185 |
| 237684 | 2012-07-22 | 21:58:45 | 1140 | 2 | 33 | 39 |
| 237683 | 2012-07-22 | 21:58:45 | 1140 | 5 | 32 | 40 |
| 237682 | 2012-07-22 | 21:58:45 | 1140 | 1 | 31 | 2 |
| 237681 | 2012-07-22 | 21:58:45 | 1140 | 25 | 30 | 412 |
| 237680 | 2012-07-22 | 21:58:45 | 1140 | 4 | 29 | 127 |
| 237679 | 2012-07-22 | 21:58:45 | 1140 | 1 | 28 | 19 |
| 237678 | 2012-07-22 | 21:58:45 | 1140 | 1 | 27 | 3 |
| 237677 | 2012-07-22 | 21:58:45 | 1140 | 3 | 26 | 6 |
| 237676 | 2012-07-22 | 21:58:45 | 1140 | 54 | 25 | 1206 |
| 237675 | 2012-07-22 | 21:58:45 | 1140 | 6 | 24 | 94 |
| 237674 | 2012-07-22 | 21:58:45 | 1140 | 10 | 23 | 600 |
| 237673 | 2012-07-22 | 21:58:45 | 1140 | 23 | 22 | 975 |
| 237672 | 2012-07-22 | 21:58:45 | 1140 | 2 | 21 | 273 |
| 237671 | 2012-07-22 | 21:58:45 | 1140 | 1 | 20 | 12 |
| 204638 | 2012-07-22 | 17:51:59 | 1140 | 11 | 40 | 168 |
| 204637 | 2012-07-22 | 17:51:59 | 1140 | 1 | 39 | 1 |
| 204636 | 2012-07-22 | 17:51:59 | 1140 | 3 | 38 | 12 |
| 204635 | 2012-07-22 | 17:51:59 | 1140 | 13 | 37 | 197 |
| 204634 | 2012-07-22 | 17:51:59 | 1140 | 1 | 36 | 10 |
| 204633 | 2012-07-22 | 17:51:59 | 1140 | 4 | 35 | 130 |
| 204632 | 2012-07-22 | 17:51:59 | 1140 | 2 | 33 | 39 |
| 204631 | 2012-07-22 | 17:51:59 | 1140 | 5 | 32 | 40 |
| 204630 | 2012-07-22 | 17:51:59 | 1140 | 1 | 31 | 2 |
| 204629 | 2012-07-22 | 17:51:59 | 1140 | 20 | 30 | 330 |
| 204628 | 2012-07-22 | 17:51:59 | 1140 | 4 | 29 | 127 |
| 204627 | 2012-07-22 | 17:51:59 | 1140 | 1 | 28 | 19 |
| 204626 | 2012-07-22 | 17:51:59 | 1140 | 1 | 27 | 3 |
| 204625 | 2012-07-22 | 17:51:59 | 1140 | 2 | 26 | 3 |
| 204624 | 2012-07-22 | 17:51:59 | 1140 | 38 | 25 | 746 |
| 204623 | 2012-07-22 | 17:51:59 | 1140 | 5 | 24 | 64 |
| 204622 | 2012-07-22 | 17:51:59 | 1140 | 9 | 23 | 351 |
| 204621 | 2012-07-22 | 17:51:59 | 1140 | 16 | 22 | 2504 |
| 204620 | 2012-07-22 | 17:51:59 | 1140 | 17 | 21 | 1157 |
| 204619 | 2012-07-22 | 17:51:59 | 1140 | 3 | 20 | 707 |
| 173562 | 2012-07-22 | 13:46:43 | 1140 | 5 | 45 | 50 |
| 173561 | 2012-07-22 | 13:46:43 | 1140 | 1 | 44 | 2 |
| 173560 | 2012-07-22 | 13:46:43 | 1140 | 11 | 40 | 168 |
| 173559 | 2012-07-22 | 13:46:43 | 1140 | 1 | 39 | 1 |
| 173558 | 2012-07-22 | 13:46:43 | 1140 | 6 | 38 | 32 |
| 173557 | 2012-07-22 | 13:46:43 | 1140 | 13 | 37 | 197 |
| 173556 | 2012-07-22 | 13:46:43 | 1140 | 3 | 35 | 105 |
| 173555 | 2012-07-22 | 13:46:43 | 1140 | 2 | 33 | 39 |
| 173554 | 2012-07-22 | 13:46:43 | 1140 | 5 | 32 | 40 |
| 173553 | 2012-07-22 | 13:46:43 | 1140 | 1 | 31 | 2 |
| 173552 | 2012-07-22 | 13:46:43 | 1140 | 16 | 30 | 218 |
| 173551 | 2012-07-22 | 13:46:43 | 1140 | 3 | 29 | 124 |
| 173550 | 2012-07-22 | 13:46:43 | 1140 | 1 | 28 | 19 |
| 173549 | 2012-07-22 | 13:46:43 | 1140 | 2 | 27 | 51 |
| 173548 | 2012-07-22 | 13:46:43 | 1140 | 1 | 26 | 1 |
| 173547 | 2012-07-22 | 13:46:43 | 1140 | 24 | 25 | 634 |
| 173546 | 2012-07-22 | 13:46:43 | 1140 | 6 | 24 | 539 |
| 173545 | 2012-07-22 | 13:46:43 | 1140 | 8 | 23 | 363 |
| 173544 | 2012-07-22 | 13:46:43 | 1140 | 6 | 22 | 170 |
| 173543 | 2012-07-22 | 13:46:43 | 1140 | 7 | 21 | 708 |
| 144198 | 2012-07-22 | 09:42:27 | 1140 | 6 | 38 | 32 |
| 144197 | 2012-07-22 | 09:42:27 | 1140 | 12 | 37 | 185 |
| 144196 | 2012-07-22 | 09:42:27 | 1140 | 2 | 35 | 103 |
| 144195 | 2012-07-22 | 09:42:27 | 1140 | 1 | 33 | 24 |
| 144194 | 2012-07-22 | 09:42:27 | 1140 | 4 | 32 | 36 |
| 144193 | 2012-07-22 | 09:42:27 | 1140 | 1 | 31 | 2 |
| 144192 | 2012-07-22 | 09:42:27 | 1140 | 14 | 30 | 184 |
| 144191 | 2012-07-22 | 09:42:27 | 1140 | 3 | 29 | 124 |
| 144190 | 2012-07-22 | 09:42:27 | 1140 | 1 | 28 | 19 |
| 144189 | 2012-07-22 | 09:42:27 | 1140 | 2 | 27 | 51 |
| 144188 | 2012-07-22 | 09:42:27 | 1140 | 1 | 26 | 1 |
| 144187 | 2012-07-22 | 09:42:27 | 1140 | 21 | 25 | 517 |
| 144186 | 2012-07-22 | 09:42:27 | 1140 | 3 | 24 | 23 |
| 144185 | 2012-07-22 | 09:42:27 | 1140 | 1 | 23 | 3 |
| 144184 | 2012-07-22 | 09:42:27 | 1140 | 2 | 22 | 122 |
| 144183 | 2012-07-22 | 09:42:27 | 1140 | 1 | 21 | 9 |
| 144182 | 2012-07-22 | 09:42:27 | 1140 | 58 | 20 | 1381 |
| 144181 | 2012-07-22 | 09:42:27 | 1140 | 13 | 19 | 440 |
| 144180 | 2012-07-22 | 09:42:27 | 1140 | 22 | 18 | 661 |
| 144179 | 2012-07-22 | 09:42:27 | 1140 | 106 | 17 | 3266 |
| 116584 | 2012-07-22 | 05:38:19 | 1140 | 2 | 35 | 103 |
| 116583 | 2012-07-22 | 05:38:19 | 1140 | 1 | 33 | 24 |
| 116582 | 2012-07-22 | 05:38:19 | 1140 | 4 | 32 | 36 |
| 116581 | 2012-07-22 | 05:38:19 | 1140 | 1 | 31 | 2 |
| 116580 | 2012-07-22 | 05:38:19 | 1140 | 13 | 30 | 181 |
| 116579 | 2012-07-22 | 05:38:19 | 1140 | 4 | 29 | 133 |
| 116578 | 2012-07-22 | 05:38:19 | 1140 | 1 | 28 | 19 |
| 116577 | 2012-07-22 | 05:38:19 | 1140 | 2 | 27 | 51 |
| 116576 | 2012-07-22 | 05:38:19 | 1140 | 1 | 26 | 1 |
| 116575 | 2012-07-22 | 05:38:19 | 1140 | 17 | 25 | 625 |
| 116574 | 2012-07-22 | 05:38:19 | 1140 | 3 | 24 | 23 |
| 116573 | 2012-07-22 | 05:38:19 | 1140 | 1 | 23 | 3 |
| 116572 | 2012-07-22 | 05:38:19 | 1140 | 2 | 22 | 122 |
| 116571 | 2012-07-22 | 05:38:19 | 1140 | 1 | 21 | 9 |
| 116570 | 2012-07-22 | 05:38:19 | 1140 | 54 | 20 | 1571 |
| 116569 | 2012-07-22 | 05:38:19 | 1140 | 11 | 19 | 252 |
| 116568 | 2012-07-22 | 05:38:19 | 1140 | 19 | 18 | 603 |
| 116567 | 2012-07-22 | 05:38:19 | 1140 | 19 | 17 | 5106 |
| 116566 | 2012-07-22 | 05:38:19 | 1140 | 11 | 16 | 531 |
| 116565 | 2012-07-22 | 05:38:19 | 1140 | 3 | 15 | 132 |
| 91347 | 2012-07-22 | 01:38:30 | 1140 | 1 | 33 | 24 |
| 91346 | 2012-07-22 | 01:38:30 | 1140 | 5 | 32 | 133 |
| 91345 | 2012-07-22 | 01:38:30 | 1140 | 1 | 31 | 2 |
| 91344 | 2012-07-22 | 01:38:30 | 1140 | 13 | 30 | 181 |
| 91343 | 2012-07-22 | 01:38:30 | 1140 | 4 | 29 | 133 |
| 91342 | 2012-07-22 | 01:38:30 | 1140 | 2 | 28 | 69 |
| 91341 | 2012-07-22 | 01:38:30 | 1140 | 3 | 27 | 69 |
| 91340 | 2012-07-22 | 01:38:30 | 1140 | 1 | 26 | 1 |
| 91339 | 2012-07-22 | 01:38:30 | 1140 | 15 | 25 | 474 |
| 91338 | 2012-07-22 | 01:38:30 | 1140 | 3 | 24 | 23 |
| 91337 | 2012-07-22 | 01:38:30 | 1140 | 1 | 23 | 3 |
| 91336 | 2012-07-22 | 01:38:30 | 1140 | 2 | 22 | 122 |
| 91335 | 2012-07-22 | 01:38:30 | 1140 | 1 | 21 | 9 |
| 91334 | 2012-07-22 | 01:38:30 | 1140 | 46 | 20 | 1591 |
| 91333 | 2012-07-22 | 01:38:30 | 1140 | 10 | 19 | 196 |
| 91332 | 2012-07-22 | 01:38:30 | 1140 | 19 | 18 | 431 |
| 91331 | 2012-07-22 | 01:38:30 | 1140 | 7 | 17 | 2791 |
| 91330 | 2012-07-22 | 01:38:30 | 1140 | 22 | 16 | 2202 |
| 91329 | 2012-07-22 | 01:38:30 | 1140 | 1 | 15 | 30 |
| 91328 | 2012-07-22 | 01:38:30 | 1140 | 2 | 14 | 2 |
+--------+--------------+--------------+---------+----------+------------+----------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment