List of regions with "last updated"

pinballmap / pbm

Pinball Map

https://pinballmap.com

GNU General Public License v3.0

108 stars 25 forks source link

List of regions with "last updated" #443

Closed RyanTG closed 8 years ago

RyanTG commented 8 years ago

Can you give me a list of all the regions, and include when each one was last updated? I'm just trying to get an idea of which ones are getting stale.

scottwainstock commented 8 years ago

I think this is somewhat accurate... I have the last time an admin made an update, last time a machine was added to a location, and last time a location object was updated.

      name       | last_admin_update | latest_lmx_added | latest_location_update
-----------------+-------------------+------------------+------------------------
 bakersfield     | 2014-11-25        | 2016-01-18       | 2016-02-25
 southflorida    | 2014-12-16        | 2015-09-06       | 2016-02-24
 portland-maine  | 2015-02-15        | 2016-02-19       | 2016-02-19
 massachusetts   | 2015-04-10        | 2016-01-10       | 2016-02-24
 nebraska        | 2015-04-20        | 2016-02-24       | 2016-02-24
 charlotte       | 2015-05-29        | 2015-07-22       | 2016-02-29
 tallahassee     | 2015-07-20        | 2015-07-20       | 2016-01-24
 philadelphia    | 2015-07-27        | 2016-01-24       | 2016-02-27
 richmond        | 2015-08-06        | 2016-02-13       | 2016-02-27
 springfield     | 2015-08-19        | 2016-02-20       | 2016-02-20
 oceancity       | 2015-09-21        | 2015-09-15       | 2015-12-25
 sacramento      | 2015-11-05        | 2016-02-05       | 2016-02-29
 birmingham      | 2015-11-06        | 2015-11-06       | 2015-12-10
 lasvegas        | 2015-11-09        | 2016-01-30       | 2016-02-13
 cleveland       | 2015-11-13        | 2016-03-01       | 2016-03-01
 dayton          | 2015-11-20        | 2016-02-29       | 2016-02-29
 charleston      | 2015-12-01        | 2016-02-10       | 2016-02-24
 champaign       | 2015-12-14        | 2016-01-02       | 2016-02-25
 new-hampshire   | 2015-12-22        | 2016-01-25       | 2016-02-04
 newjersey       | 2016-01-02        | 2016-02-27       | 2016-02-27
 stlouis         | 2016-01-07        | 2016-02-08       | 2016-02-21
 maryland-north  | 2016-01-08        | 2016-02-17       | 2016-02-17
 eugene          | 2016-01-08        | 2016-02-29       | 2016-02-29
 connecticut     | 2016-01-14        | 2016-01-14       | 2016-01-24
 hudsonvalley    | 2016-01-18        | 2016-02-03       | 2016-02-29
 rochester       | 2016-01-26        | 2016-02-26       | 2016-02-26
 utah            | 2016-01-26        | 2016-02-22       | 2016-03-01
 columbus        | 2016-01-28        | 2016-02-29       | 2016-03-03
 albuquerque     | 2016-01-29        | 2016-01-16       | 2016-02-28
 burlington      | 2016-02-01        | 2016-02-23       | 2016-02-23
 orlando         | 2016-02-02        | 2016-03-02       | 2016-03-02
 austin          | 2016-02-06        | 2016-02-14       | 2016-03-02
 colorado        | 2016-02-11        | 2016-02-29       | 2016-03-02
 bellingham      | 2016-02-13        | 2016-01-09       | 2016-01-09
 houston         | 2016-02-14        | 2016-02-14       | 2016-02-28
 dc              | 2016-02-15        | 2016-03-02       | 2016-03-02
 louisville      | 2016-02-15        | 2016-02-29       | 2016-02-29
 michigan-west   | 2016-02-15        | 2016-02-20       | 2016-03-02
 rhode-island    | 2016-02-16        | 2016-02-26       | 2016-02-26
 asheville       | 2016-02-17        | 2016-02-17       | 2016-02-17
 jacksonville    | 2016-02-17        | 2016-02-29       | 2016-02-29
 iowa            | 2016-02-18        | 2016-03-03       | 2016-03-03
 baltimore       | 2016-02-19        | 2016-02-19       | 2016-02-19
 dfw             | 2016-02-19        | 2016-02-19       | 2016-02-29
 seattle         | 2016-02-19        | 2016-02-28       | 2016-02-28
 west-virginia   | 2016-02-21        | 2016-02-20       | 2016-02-25
 ottawa          | 2016-02-22        | 2016-02-22       | 2016-02-22
 spokane         | 2016-02-23        | 2016-02-19       | 2016-02-19
 bc              | 2016-02-23        | 2016-02-23       | 2016-02-26
 syracuse        | 2016-02-24        | 2016-02-24       | 2016-02-28
 kansascity      | 2016-02-24        | 2016-02-26       | 2016-03-01
 reno            | 2016-02-25        | 2016-02-12       | 2016-03-02
 phoenix         | 2016-02-25        | 2016-02-25       | 2016-02-27
 twincities      | 2016-02-25        | 2016-02-28       | 2016-03-02
 bayarea         | 2016-02-26        | 2016-03-03       | 2016-03-03
 sandiego        | 2016-02-26        | 2016-03-02       | 2016-03-02
 central-indiana | 2016-02-28        | 2016-02-28       | 2016-02-28
 pittsburgh      | 2016-02-29        | 2016-02-29       | 2016-02-29
 tampabay        | 2016-02-29        | 2016-02-29       | 2016-02-29
 centralpa       | 2016-02-29        | 2016-02-29       | 2016-02-29
 nyc             | 2016-03-01        | 2016-03-01       | 2016-03-01
 wisconsin       | 2016-03-01        | 2016-03-01       | 2016-03-03
 chicago         | 2016-03-02        | 2016-03-02       | 2016-03-02
 la              | 2016-03-02        | 2016-03-02       | 2016-03-03
 redding         | 2016-03-02        | 2016-03-03       | 2016-03-03
 portland        | 2016-03-02        | 2016-02-29       | 2016-03-03
 oregon-south    | 2016-03-02        | 2016-03-02       | 2016-03-02
 boise           | 2016-03-02        | 2016-03-02       | 2016-03-02
(68 rows)

scottwainstock commented 8 years ago

Here's the SQL I used for this, BTW....

select
    r.name,
    max(a.created_at)::date as last_admin_update,
    max(lmx.created_at)::date as latest_lmx_added,
    max(l.updated_at)::date as latest_location_update
from
    rails_admin_histories a
    inner join users u on u.email = a.username
    inner join regions r on u.region_id = r.id
    inner join locations l on l.region_id = r.id
    inner join location_machine_xrefs lmx on lmx.location_id = l.id
group by 1
order by 2;

scottwainstock commented 8 years ago

Here's the date of the most recent location suggestion for each region...

      name       | most_recent_location_suggestion
-----------------+---------------------------------
 birmingham      | 2015-10-22
 eugene          | 2015-10-30
 lasvegas        | 2015-11-08
 bellingham      | 2015-11-10
 charleston      | 2015-11-18
 dayton          | 2015-11-20
 champaign       | 2015-11-24
 tallahassee     | 2015-11-25
 austin          | 2015-12-10
 syracuse        | 2015-12-19
 bakersfield     | 2015-12-22
 central-indiana | 2015-12-26
 michigan-west   | 2015-12-27
 hudsonvalley    | 2015-12-31
 asheville       | 2015-12-31
 newjersey       | 2015-12-31
 baltimore       | 2016-01-01
 maryland-north  | 2016-01-08
 albuquerque     | 2016-01-09
 jacksonville    | 2016-01-20
 new-hampshire   | 2016-01-23
 utah            | 2016-01-24
 reno            | 2016-01-25
 cleveland       | 2016-01-26
 sacramento      | 2016-01-30
 nyc             | 2016-02-01
 stlouis         | 2016-02-03
 nebraska        | 2016-02-05
 dfw             | 2016-02-05
 columbus        | 2016-02-06
 philadelphia    | 2016-02-07
 redding         | 2016-02-09
 dc              | 2016-02-11
 louisville      | 2016-02-13
 pittsburgh      | 2016-02-14
 houston         | 2016-02-14
 sandiego        | 2016-02-14
 colorado        | 2016-02-15
 oregon-south    | 2016-02-15
 phoenix         | 2016-02-16
 iowa            | 2016-02-18
 west-virginia   | 2016-02-18
 massachusetts   | 2016-02-20
 bc              | 2016-02-22
 connecticut     | 2016-02-22
 twincities      | 2016-02-24
 spokane         | 2016-02-24
 richmond        | 2016-02-26
 orlando         | 2016-02-26
 rhode-island    | 2016-02-26
 bayarea         | 2016-02-26
 detroit         | 2016-02-26
 la              | 2016-02-27
 seattle         | 2016-02-27
 tampabay        | 2016-02-28
 centralpa       | 2016-02-29
 charlotte       | 2016-02-29
 wisconsin       | 2016-02-29
 kansascity      | 2016-03-01
 rochester       | 2016-03-01
 boise           | 2016-03-02
 toronto         | 2016-03-02
 southflorida    | 2016-03-03
 chicago         | 2016-03-03
 portland        | 2016-03-03
(65 rows)

RyanTG commented 8 years ago

This is great! I'll look at it in more detail tomorrow.

On March 2, 2016 9:46:55 PM PST, Scott Wainstock notifications@github.com wrote:

Here's the date of the most recent location suggestion for each region...

     name       | most_recent_location_suggestion
-----------------+---------------------------------
birmingham      | 2015-10-22
eugene          | 2015-10-30
lasvegas        | 2015-11-08
bellingham      | 2015-11-10
charleston      | 2015-11-18
dayton          | 2015-11-20
champaign       | 2015-11-24
tallahassee     | 2015-11-25
austin          | 2015-12-10
syracuse        | 2015-12-19
bakersfield     | 2015-12-22
central-indiana | 2015-12-26
michigan-west   | 2015-12-27
hudsonvalley    | 2015-12-31
asheville       | 2015-12-31
newjersey       | 2015-12-31
baltimore       | 2016-01-01
maryland-north  | 2016-01-08
albuquerque     | 2016-01-09
jacksonville    | 2016-01-20
new-hampshire   | 2016-01-23
utah            | 2016-01-24
reno            | 2016-01-25
cleveland       | 2016-01-26
sacramento      | 2016-01-30
nyc             | 2016-02-01
stlouis         | 2016-02-03
nebraska        | 2016-02-05
dfw             | 2016-02-05
columbus        | 2016-02-06
philadelphia    | 2016-02-07
redding         | 2016-02-09
dc              | 2016-02-11
louisville      | 2016-02-13
pittsburgh      | 2016-02-14
houston         | 2016-02-14
sandiego        | 2016-02-14
colorado        | 2016-02-15
oregon-south    | 2016-02-15
phoenix         | 2016-02-16
iowa            | 2016-02-18
west-virginia   | 2016-02-18
massachusetts   | 2016-02-20
bc              | 2016-02-22
connecticut     | 2016-02-22
twincities      | 2016-02-24
spokane         | 2016-02-24
richmond        | 2016-02-26
orlando         | 2016-02-26
rhode-island    | 2016-02-26
bayarea         | 2016-02-26
detroit         | 2016-02-26
la              | 2016-02-27
seattle         | 2016-02-27
tampabay        | 2016-02-28
centralpa       | 2016-02-29
charlotte       | 2016-02-29
wisconsin       | 2016-02-29
kansascity      | 2016-03-01
rochester       | 2016-03-01
boise           | 2016-03-02
toronto         | 2016-03-02
southflorida    | 2016-03-03
chicago         | 2016-03-03
portland        | 2016-03-03
(65 rows)

Reply to this email directly or view it on GitHub: https://github.com/scottwainstock/pbm/issues/443#issuecomment-191593045

Sent from my Android device with K-9 Mail. Please excuse my brevity.

RyanTG commented 8 years ago

Not sure why, but a couple regions are left off these lists. In the first one, we have 70 regions and it's listing 68. Detroit is missing - not sure about the other one. The Detroit admin hasn't logged in since 2014. I added a location to it the other day.

And the 2nd list has 65. Maybe the missing ones are from before we have dates for activities...

On 03/02/2016 09:49 PM, Ryan Taylor-Gratzer wrote:

This is great! I'll look at it in more detail tomorrow.

On March 2, 2016 9:46:55 PM PST, Scott Wainstock notifications@github.com wrote:

Here's the date of the most recent location suggestion for each
region...

|name | most_recent_location_suggestion
-----------------+--------------------------------- birmingham |
2015-10-22 eugene | 2015-10-30 lasvegas | 2015-11-08 bellingham |
2015-11-10 charleston | 2015-11-18 dayton | 2015-11-20 champaign |
2015-11-24 tallahassee | 2015-11-25 austin | 2015-12-10 syracuse |
2015-12-19 bakersfield | 2015-12-22 central-indiana | 2015-12-26
michigan-west | 2015-12-27 hudsonvalley | 2015-12-31 asheville |
2015-12-31 newjersey | 2015-12-31 baltimore | 2016-01-01
maryland-north | 2016-01-08 albuquerque | 2016-01-09 jacksonville |
2016-01-20 new-hampshire | 2016-01-23 utah | 2016-01-24 reno |
2016-01-25 cleveland | 2016-01-26 sacramento | 2016-01-30 nyc |
2016-02-01 stlouis | 2016-02-03 nebraska | 2016-02-05 dfw |
2016-02-05 columbus | 2016-02-06 philadelphia | 2016-02-07 redding |
2016-02-09 dc | 2016-02-11 louisville | 2016-02-13 pittsburgh |
2016-02-14 houston | 2016-02-14 sandiego | 2016-02-14 colorado |
2016-02-15 oregon-south | 2016-02-15 phoenix | 2016-02-16 iowa |
2016-02-18 west-virginia | 2016-02-18 massachusetts | 2016-02-20 bc
| 2016-02-22 connecticut | 2016-02-22 twincities | 2016-02-24
spokane | 2016-02-24 richmond | 2016-02-26 orlando | 2016-02-26
rhode-island | 2016-02-26 bayarea | 2016-02-26 detroit | 2016-02-26
la | 2016-02-27 seattle | 2016-02-27 tampabay | 2016-02-28 centralpa
| 2016-02-29 charlotte | 2016-02-29 wisconsin | 2016-02-29
kansascity | 2016-03-01 rochester | 2016-03-01 boise | 2016-03-02
toronto | 2016-03-02 southflorida | 2016-03-03 chicago | 2016-03-03
portland | 2016-03-03 (65 rows) |

—
Reply to this email directly or view it on GitHub
<https://github.com/scottwainstock/pbm/issues/443#issuecomment-191593045>.

Sent from my Android device with K-9 Mail. Please excuse my brevity.

scottwainstock commented 8 years ago

We haven't been keeping track of this data since the start of the site... so if it's not on the list, we haven't recorded a location suggestion for it.

On Thu, Mar 3, 2016 at 7:39 AM, RyanTG notifications@github.com wrote:

Not sure why, but a couple regions are left off these lists. In the first one, we have 70 regions and it's listing 68. Detroit is missing - not sure about the other one. The Detroit admin hasn't logged in since 2014. I added a location to it the other day.

And the 2nd list has 65. Maybe the missing ones are from before we have dates for activities...

On 03/02/2016 09:49 PM, Ryan Taylor-Gratzer wrote:

This is great! I'll look at it in more detail tomorrow.

On March 2, 2016 9:46:55 PM PST, Scott Wainstock notifications@github.com wrote:

Here's the date of the most recent location suggestion for each region...

|name | most_recent_location_suggestion -----------------+--------------------------------- birmingham | 2015-10-22 eugene | 2015-10-30 lasvegas | 2015-11-08 bellingham | 2015-11-10 charleston | 2015-11-18 dayton | 2015-11-20 champaign | 2015-11-24 tallahassee | 2015-11-25 austin | 2015-12-10 syracuse | 2015-12-19 bakersfield | 2015-12-22 central-indiana | 2015-12-26 michigan-west | 2015-12-27 hudsonvalley | 2015-12-31 asheville | 2015-12-31 newjersey | 2015-12-31 baltimore | 2016-01-01 maryland-north | 2016-01-08 albuquerque | 2016-01-09 jacksonville | 2016-01-20 new-hampshire | 2016-01-23 utah | 2016-01-24 reno | 2016-01-25 cleveland | 2016-01-26 sacramento | 2016-01-30 nyc | 2016-02-01 stlouis | 2016-02-03 nebraska | 2016-02-05 dfw | 2016-02-05 columbus | 2016-02-06 philadelphia | 2016-02-07 redding | 2016-02-09 dc | 2016-02-11 louisville | 2016-02-13 pittsburgh | 2016-02-14 houston | 2016-02-14 sandiego | 2016-02-14 colorado | 2016-02-15 oregon-south | 2016-02-15 phoenix | 2016-02-16 iowa | 2016-02-18 west-virginia | 2016-02-18 massachusetts | 2016-02-20 bc | 2016-02-22 connecticut | 2016-02-22 twincities | 2016-02-24 spokane | 2016-02-24 richmond | 2016-02-26 orlando | 2016-02-26 rhode-island | 2016-02-26 bayarea | 2016-02-26 detroit | 2016-02-26 la | 2016-02-27 seattle | 2016-02-27 tampabay | 2016-02-28 centralpa | 2016-02-29 charlotte | 2016-02-29 wisconsin | 2016-02-29 kansascity | 2016-03-01 rochester | 2016-03-01 boise | 2016-03-02 toronto | 2016-03-02 southflorida | 2016-03-03 chicago | 2016-03-03 portland | 2016-03-03 (65 rows) |

— Reply to this email directly or view it on GitHub <https://github.com/scottwainstock/pbm/issues/443#issuecomment-191593045 .

Sent from my Android device with K-9 Mail. Please excuse my brevity.

— Reply to this email directly or view it on GitHub https://github.com/scottwainstock/pbm/issues/443#issuecomment-191818497.

RyanTG commented 8 years ago

And in the first case, if it's not on the list, then the admin probably hasn't logged in since we started keeping track of this data (since that seems to be the sort order of that list). The other one missing is Toronto. That admin has NEVER logged in. But that map gets used pretty often.

On 03/03/2016 08:59 AM, Scott Wainstock wrote:

We haven't been keeping track of this data since the start of the site... so if it's not on the list, we haven't recorded a location suggestion for it.

On Thu, Mar 3, 2016 at 7:39 AM, RyanTG notifications@github.com wrote:

Not sure why, but a couple regions are left off these lists. In the first one, we have 70 regions and it's listing 68. Detroit is missing - not sure about the other one. The Detroit admin hasn't logged in since 2014. I added a location to it the other day.

And the 2nd list has 65. Maybe the missing ones are from before we have dates for activities...

On 03/02/2016 09:49 PM, Ryan Taylor-Gratzer wrote:

This is great! I'll look at it in more detail tomorrow.

On March 2, 2016 9:46:55 PM PST, Scott Wainstock notifications@github.com wrote:

Here's the date of the most recent location suggestion for each region...

|name | most_recent_location_suggestion -----------------+--------------------------------- birmingham | 2015-10-22 eugene | 2015-10-30 lasvegas | 2015-11-08 bellingham | 2015-11-10 charleston | 2015-11-18 dayton | 2015-11-20 champaign | 2015-11-24 tallahassee | 2015-11-25 austin | 2015-12-10 syracuse | 2015-12-19 bakersfield | 2015-12-22 central-indiana | 2015-12-26 michigan-west | 2015-12-27 hudsonvalley | 2015-12-31 asheville | 2015-12-31 newjersey | 2015-12-31 baltimore | 2016-01-01 maryland-north | 2016-01-08 albuquerque | 2016-01-09 jacksonville | 2016-01-20 new-hampshire | 2016-01-23 utah | 2016-01-24 reno | 2016-01-25 cleveland | 2016-01-26 sacramento | 2016-01-30 nyc | 2016-02-01 stlouis | 2016-02-03 nebraska | 2016-02-05 dfw | 2016-02-05 columbus | 2016-02-06 philadelphia | 2016-02-07 redding | 2016-02-09 dc | 2016-02-11 louisville | 2016-02-13 pittsburgh | 2016-02-14 houston | 2016-02-14 sandiego | 2016-02-14 colorado | 2016-02-15 oregon-south | 2016-02-15 phoenix | 2016-02-16 iowa | 2016-02-18 west-virginia | 2016-02-18 massachusetts | 2016-02-20 bc | 2016-02-22 connecticut | 2016-02-22 twincities | 2016-02-24 spokane | 2016-02-24 richmond | 2016-02-26 orlando | 2016-02-26 rhode-island | 2016-02-26 bayarea | 2016-02-26 detroit | 2016-02-26 la | 2016-02-27 seattle | 2016-02-27 tampabay | 2016-02-28 centralpa | 2016-02-29 charlotte | 2016-02-29 wisconsin | 2016-02-29 kansascity | 2016-03-01 rochester | 2016-03-01 boise | 2016-03-02 toronto | 2016-03-02 southflorida | 2016-03-03 chicago | 2016-03-03 portland | 2016-03-03 (65 rows) |

— Reply to this email directly or view it on GitHub <https://github.com/scottwainstock/pbm/issues/443#issuecomment-191593045 .

Sent from my Android device with K-9 Mail. Please excuse my brevity.

— Reply to this email directly or view it on GitHub https://github.com/scottwainstock/pbm/issues/443#issuecomment-191818497.

— Reply to this email directly or view it on GitHub https://github.com/scottwainstock/pbm/issues/443#issuecomment-191859031.