Last active
January 25, 2019 20:35
-
-
Save genhernandez/d0e11cda2bb468884379cfacaf142865 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "cells": [ | |
| { | |
| "cell_type": "code", | |
| "execution_count": 92, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "import os\n", | |
| "import json\n", | |
| "from sqlalchemy import create_engine\n", | |
| "import pandas as pd\n", | |
| "dwh_creds = os.getenv('DWH_CREDS')\n", | |
| "mb_creds = os.getenv('MB_CREDS')\n", | |
| "dwh_engine = create_engine(dwh_creds)\n", | |
| "mb_engine = create_engine(mb_creds)\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 112, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "open_interval_event_ids_query = \"\"\"\n", | |
| "select distinct id\n", | |
| "from locations\n", | |
| "where upper_inf(time_range) AND lower(time_range) < ('1547482980'::int::abstime::timestamp at time zone 'UTC')\n", | |
| "and org_id = 583 and provider_id = '63f13c48-34ff-49d2-aca7-cf6a5b6171c3'\n", | |
| "\"\"\"\n", | |
| "\n", | |
| "open_interval_event_ids = pd.read_sql(open_interval_event_ids_query, con=mb_engine)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 113, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": [ | |
| "361" | |
| ] | |
| }, | |
| "execution_count": 113, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "len(open_interval_event_ids.id)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 114, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "open_interval_event_ids_str = \",\".join([f\"'{s}'\" for s in open_interval_event_ids.id])\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 126, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "n_devices_with_open_interval_before_timestamp_query = f\"\"\"\n", | |
| "select n_open_interval_events, count(*) as n_devices from\n", | |
| "(select device_id, count(*) as n_open_interval_events from events \n", | |
| "where id in ({open_interval_event_ids_str})\n", | |
| "group by device_id) as b\n", | |
| "group by n_open_interval_events\n", | |
| "\"\"\"\n", | |
| "\n", | |
| "n_devices_res = pd.read_sql(n_devices_with_open_interval_before_timestamp_query, con=dwh_engine)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 136, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": [ | |
| "355" | |
| ] | |
| }, | |
| "execution_count": 136, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "n_devices_res\n", | |
| "n_devices_res.n_devices.sum()" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 128, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "device_ids_with_open_interval_before_timestamp_query = f\"\"\"\n", | |
| "select distinct device_id\n", | |
| "from events\n", | |
| "where id in ({open_interval_event_ids_str})\n", | |
| "\"\"\"\n", | |
| "\n", | |
| "device_ids_res = pd.read_sql(device_ids_with_open_interval_before_timestamp_query, con=dwh_engine)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 129, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>device_id</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>0</th>\n", | |
| " <td>090858dd-2d3e-4145-bb8d-eff9ab35bc35</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " <td>09760f7a-2c69-4810-b95f-e9093ece6d46</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2</th>\n", | |
| " <td>0998a5cd-0a16-43c9-bca5-7cb84628b03c</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>3</th>\n", | |
| " <td>0a187031-ae16-470e-b9e4-13ff202e0891</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>4</th>\n", | |
| " <td>0a51908c-63df-4d3c-ba9e-351bdc32997b</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>5</th>\n", | |
| " <td>0ab83bdf-c093-43ec-be2f-135617627c29</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>6</th>\n", | |
| " <td>0b5853da-f9b3-4317-b5dc-6a6d6162edb7</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>7</th>\n", | |
| " <td>0c945287-eb78-4d04-b311-69ea9bba1a37</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>8</th>\n", | |
| " <td>0cfbb775-86b9-4591-b098-630f512a17af</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>9</th>\n", | |
| " <td>0dcec5bc-b80c-46c3-b192-c19edcf7b7d8</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>10</th>\n", | |
| " <td>0e02082a-4804-45b3-bf01-e0f4cd8a33d2</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>11</th>\n", | |
| " <td>106b7436-c33e-491e-b67f-3ad0155c6930</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>12</th>\n", | |
| " <td>10e4ae2c-eaa9-4ccc-ba54-2caeaa961d21</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>13</th>\n", | |
| " <td>11356930-a9e4-4875-bf3c-8b48de3a7ca7</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>14</th>\n", | |
| " <td>11e23e21-72ee-43f4-b0aa-48376da4eb82</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>15</th>\n", | |
| " <td>126f2109-df1d-46c7-be36-940a38ab35ec</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>16</th>\n", | |
| " <td>126fd3a0-4b7e-49e4-b1f1-6f12da4ee88b</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>17</th>\n", | |
| " <td>1408546d-8222-42c5-b8f9-64e112132632</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>18</th>\n", | |
| " <td>14cd55c4-3300-4e72-b66b-70b71f855512</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>19</th>\n", | |
| " <td>14e6028f-bd5d-45f4-bceb-dd495bd605aa</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>20</th>\n", | |
| " <td>14f57522-a839-46ee-b7f5-0d81e739dfb7</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>21</th>\n", | |
| " <td>15db41eb-eba6-43c3-bc67-33d653a79735</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>22</th>\n", | |
| " <td>1654a875-7b05-4c85-b038-71fcc4d70c0e</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>23</th>\n", | |
| " <td>1682fc0a-05a9-4990-b8da-ee03da769d94</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>24</th>\n", | |
| " <td>16eb0fc7-fc9b-4ba3-b9d2-c450a0806b75</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>25</th>\n", | |
| " <td>172315a5-5ff7-4548-b844-a4e43d9f8ba9</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>26</th>\n", | |
| " <td>17800c42-a223-430e-bbec-b73f729a210f</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>27</th>\n", | |
| " <td>17ae9674-499f-4507-b3c0-74b3c86c09a3</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>28</th>\n", | |
| " <td>17e4ff01-b6cb-46b9-b327-5b33f7eb3bd9</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>29</th>\n", | |
| " <td>1806e577-5873-4c3c-bd29-3d10a4b42c17</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>...</th>\n", | |
| " <td>...</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>325</th>\n", | |
| " <td>8c8c1e15-4da7-444b-b5fa-0f4510a972b8</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>326</th>\n", | |
| " <td>8d4e34c2-6187-499f-b8a5-f10ad85a875f</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>327</th>\n", | |
| " <td>8d7928d2-535c-4849-b4e0-ab34a1e5f972</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>328</th>\n", | |
| " <td>8d9d7896-691c-4177-b538-1f1bbc419cc0</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>329</th>\n", | |
| " <td>8dd41e26-5bf3-4583-bc38-c97a83e31b66</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>330</th>\n", | |
| " <td>8e045286-c3bd-4b88-ba3d-29137a2fb561</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>331</th>\n", | |
| " <td>8e3bf2cd-2e90-49b1-b9f2-263d16bd7f3d</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>332</th>\n", | |
| " <td>8e890e7c-44b3-4d0b-bfc1-76bbff63d572</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>333</th>\n", | |
| " <td>8e9c9e2c-9718-4de8-b5cc-14bba69aa176</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>334</th>\n", | |
| " <td>8e9ce29b-f6d7-4337-bf36-3f77bd92ceb8</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>335</th>\n", | |
| " <td>8eae4126-4fb5-495e-b32b-b6fe1fedff24</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>336</th>\n", | |
| " <td>8edb9c91-3742-4bbc-ba98-62a49507e47c</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>337</th>\n", | |
| " <td>8ee84b72-e673-43f0-b65c-7f309f30d866</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>338</th>\n", | |
| " <td>8f8a7e99-495a-440a-bebc-e9ada0229e8f</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>339</th>\n", | |
| " <td>8fb241b2-b1db-455e-b02c-27cf4441491f</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>340</th>\n", | |
| " <td>8fb35b9c-3a49-4227-b1f9-91df828ffa46</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>341</th>\n", | |
| " <td>901d7f93-0c92-48eb-b6b0-6ba50bf8ed6e</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>342</th>\n", | |
| " <td>9065c797-f8bf-422c-b88c-59e179cb67fd</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>343</th>\n", | |
| " <td>9073087f-ce99-414e-b592-85b264c5591a</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>344</th>\n", | |
| " <td>9140070d-00bd-439a-bbd2-2c223a1a1ec6</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>345</th>\n", | |
| " <td>9177e007-a0a6-488d-b1a8-c066194cd5a9</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>346</th>\n", | |
| " <td>91cf7e7a-1ca2-4c08-b677-bbee7418a7bc</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>347</th>\n", | |
| " <td>91d794a8-d97d-4922-bbbc-6c3b74cce4d5</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>348</th>\n", | |
| " <td>91e2e0af-9ec8-4f55-bf74-378f0f1c97bc</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>349</th>\n", | |
| " <td>921e01a8-f90b-4f94-b8c7-d9356650b669</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>350</th>\n", | |
| " <td>9224fd4e-b342-4d69-b9bd-bb684fe32023</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>351</th>\n", | |
| " <td>9240b0c3-3ece-487d-b0e9-e6cdf29215c8</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>352</th>\n", | |
| " <td>92a4c8b8-6ce9-4178-b3d5-99de0a12f875</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>353</th>\n", | |
| " <td>93749de6-3ebc-4af9-b8db-bd4535af1d04</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>354</th>\n", | |
| " <td>93a55c87-52b3-47fa-b315-1fac5a44961b</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "<p>355 rows × 1 columns</p>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " device_id\n", | |
| "0 090858dd-2d3e-4145-bb8d-eff9ab35bc35\n", | |
| "1 09760f7a-2c69-4810-b95f-e9093ece6d46\n", | |
| "2 0998a5cd-0a16-43c9-bca5-7cb84628b03c\n", | |
| "3 0a187031-ae16-470e-b9e4-13ff202e0891\n", | |
| "4 0a51908c-63df-4d3c-ba9e-351bdc32997b\n", | |
| "5 0ab83bdf-c093-43ec-be2f-135617627c29\n", | |
| "6 0b5853da-f9b3-4317-b5dc-6a6d6162edb7\n", | |
| "7 0c945287-eb78-4d04-b311-69ea9bba1a37\n", | |
| "8 0cfbb775-86b9-4591-b098-630f512a17af\n", | |
| "9 0dcec5bc-b80c-46c3-b192-c19edcf7b7d8\n", | |
| "10 0e02082a-4804-45b3-bf01-e0f4cd8a33d2\n", | |
| "11 106b7436-c33e-491e-b67f-3ad0155c6930\n", | |
| "12 10e4ae2c-eaa9-4ccc-ba54-2caeaa961d21\n", | |
| "13 11356930-a9e4-4875-bf3c-8b48de3a7ca7\n", | |
| "14 11e23e21-72ee-43f4-b0aa-48376da4eb82\n", | |
| "15 126f2109-df1d-46c7-be36-940a38ab35ec\n", | |
| "16 126fd3a0-4b7e-49e4-b1f1-6f12da4ee88b\n", | |
| "17 1408546d-8222-42c5-b8f9-64e112132632\n", | |
| "18 14cd55c4-3300-4e72-b66b-70b71f855512\n", | |
| "19 14e6028f-bd5d-45f4-bceb-dd495bd605aa\n", | |
| "20 14f57522-a839-46ee-b7f5-0d81e739dfb7\n", | |
| "21 15db41eb-eba6-43c3-bc67-33d653a79735\n", | |
| "22 1654a875-7b05-4c85-b038-71fcc4d70c0e\n", | |
| "23 1682fc0a-05a9-4990-b8da-ee03da769d94\n", | |
| "24 16eb0fc7-fc9b-4ba3-b9d2-c450a0806b75\n", | |
| "25 172315a5-5ff7-4548-b844-a4e43d9f8ba9\n", | |
| "26 17800c42-a223-430e-bbec-b73f729a210f\n", | |
| "27 17ae9674-499f-4507-b3c0-74b3c86c09a3\n", | |
| "28 17e4ff01-b6cb-46b9-b327-5b33f7eb3bd9\n", | |
| "29 1806e577-5873-4c3c-bd29-3d10a4b42c17\n", | |
| ".. ...\n", | |
| "325 8c8c1e15-4da7-444b-b5fa-0f4510a972b8\n", | |
| "326 8d4e34c2-6187-499f-b8a5-f10ad85a875f\n", | |
| "327 8d7928d2-535c-4849-b4e0-ab34a1e5f972\n", | |
| "328 8d9d7896-691c-4177-b538-1f1bbc419cc0\n", | |
| "329 8dd41e26-5bf3-4583-bc38-c97a83e31b66\n", | |
| "330 8e045286-c3bd-4b88-ba3d-29137a2fb561\n", | |
| "331 8e3bf2cd-2e90-49b1-b9f2-263d16bd7f3d\n", | |
| "332 8e890e7c-44b3-4d0b-bfc1-76bbff63d572\n", | |
| "333 8e9c9e2c-9718-4de8-b5cc-14bba69aa176\n", | |
| "334 8e9ce29b-f6d7-4337-bf36-3f77bd92ceb8\n", | |
| "335 8eae4126-4fb5-495e-b32b-b6fe1fedff24\n", | |
| "336 8edb9c91-3742-4bbc-ba98-62a49507e47c\n", | |
| "337 8ee84b72-e673-43f0-b65c-7f309f30d866\n", | |
| "338 8f8a7e99-495a-440a-bebc-e9ada0229e8f\n", | |
| "339 8fb241b2-b1db-455e-b02c-27cf4441491f\n", | |
| "340 8fb35b9c-3a49-4227-b1f9-91df828ffa46\n", | |
| "341 901d7f93-0c92-48eb-b6b0-6ba50bf8ed6e\n", | |
| "342 9065c797-f8bf-422c-b88c-59e179cb67fd\n", | |
| "343 9073087f-ce99-414e-b592-85b264c5591a\n", | |
| "344 9140070d-00bd-439a-bbd2-2c223a1a1ec6\n", | |
| "345 9177e007-a0a6-488d-b1a8-c066194cd5a9\n", | |
| "346 91cf7e7a-1ca2-4c08-b677-bbee7418a7bc\n", | |
| "347 91d794a8-d97d-4922-bbbc-6c3b74cce4d5\n", | |
| "348 91e2e0af-9ec8-4f55-bf74-378f0f1c97bc\n", | |
| "349 921e01a8-f90b-4f94-b8c7-d9356650b669\n", | |
| "350 9224fd4e-b342-4d69-b9bd-bb684fe32023\n", | |
| "351 9240b0c3-3ece-487d-b0e9-e6cdf29215c8\n", | |
| "352 92a4c8b8-6ce9-4178-b3d5-99de0a12f875\n", | |
| "353 93749de6-3ebc-4af9-b8db-bd4535af1d04\n", | |
| "354 93a55c87-52b3-47fa-b315-1fac5a44961b\n", | |
| "\n", | |
| "[355 rows x 1 columns]" | |
| ] | |
| }, | |
| "execution_count": 129, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "device_ids_res" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 130, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "device_ids_with_open_interval_str = \",\".join([f\"'{s}'\" for s in device_ids_res.device_id])" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 131, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "n_devices_with_open_intervals_and_la_events_query = f\"\"\"\n", | |
| "select count(distinct e.device_id) as n_devices from\n", | |
| "(select distinct on (device_id) * from events\n", | |
| "where org_id = 581 \n", | |
| "and provider_id = '63f13c48-34ff-49d2-aca7-cf6a5b6171c3' \n", | |
| "and device_id in ({device_ids_with_open_interval_str})\n", | |
| "and event_time > '2019-01-14 08:22:50-0800'\n", | |
| ") as la_events\n", | |
| "left join events as e\n", | |
| "on e.org_id = 583 and e.device_id = la_events.device_id and e.provider_id = '63f13c48-34ff-49d2-aca7-cf6a5b6171c3' and e.event_time < '2019-01-14 08:22:50-0800' \n", | |
| "\"\"\"" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 132, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "n_devices_with_open_interval_and_la_res = pd.read_sql(n_devices_with_open_intervals_and_la_events_query, con=dwh_engine)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 133, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>n_devices</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>0</th>\n", | |
| " <td>250</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " n_devices\n", | |
| "0 250" | |
| ] | |
| }, | |
| "execution_count": 133, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "n_devices_with_open_interval_and_la_res" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 134, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "query = f\"\"\"SELECT \n", | |
| "last_event.year, last_event.week,\n", | |
| "count(distinct last_event.device_id) as n_culver_devices,\n", | |
| "count(distinct la_events.device_id) as n_la_devices_from_culver\n", | |
| "FROM\n", | |
| "(\n", | |
| "select DISTINCT on (device_id) *, extract(YEAR FROM event_time) as year, extract(WEEK FROM event_time) as week\n", | |
| "from events\n", | |
| "where org_id = 583 and provider_id='63f13c48-34ff-49d2-aca7-cf6a5b6171c3'\n", | |
| "and device_id in ({device_ids_with_open_interval_str})\n", | |
| "ORDER by device_id, event_time desc\n", | |
| ") as last_event\n", | |
| "LEFT JOIN events as la_events\n", | |
| "on la_events.org_id = 581 and la_events.device_id = last_event.device_id and la_events.provider_id='63f13c48-34ff-49d2-aca7-cf6a5b6171c3' and la_events.event_time > last_event.event_time\n", | |
| "WHERE last_event.event_type in ('available', 'unavailable')\n", | |
| "GROUP BY last_event.year, last_event.week\n", | |
| "order by last_event.year, last_event.week;\"\"\"\n", | |
| "\n", | |
| "query_res = pd.read_sql(query, con=dwh_engine)" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 135, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/html": [ | |
| "<div>\n", | |
| "<style scoped>\n", | |
| " .dataframe tbody tr th:only-of-type {\n", | |
| " vertical-align: middle;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe tbody tr th {\n", | |
| " vertical-align: top;\n", | |
| " }\n", | |
| "\n", | |
| " .dataframe thead th {\n", | |
| " text-align: right;\n", | |
| " }\n", | |
| "</style>\n", | |
| "<table border=\"1\" class=\"dataframe\">\n", | |
| " <thead>\n", | |
| " <tr style=\"text-align: right;\">\n", | |
| " <th></th>\n", | |
| " <th>year</th>\n", | |
| " <th>week</th>\n", | |
| " <th>n_culver_devices</th>\n", | |
| " <th>n_la_devices_from_culver</th>\n", | |
| " </tr>\n", | |
| " </thead>\n", | |
| " <tbody>\n", | |
| " <tr>\n", | |
| " <th>0</th>\n", | |
| " <td>2018.0</td>\n", | |
| " <td>1.0</td>\n", | |
| " <td>3</td>\n", | |
| " <td>3</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>1</th>\n", | |
| " <td>2018.0</td>\n", | |
| " <td>48.0</td>\n", | |
| " <td>21</td>\n", | |
| " <td>21</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>2</th>\n", | |
| " <td>2018.0</td>\n", | |
| " <td>49.0</td>\n", | |
| " <td>38</td>\n", | |
| " <td>35</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>3</th>\n", | |
| " <td>2018.0</td>\n", | |
| " <td>50.0</td>\n", | |
| " <td>40</td>\n", | |
| " <td>40</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>4</th>\n", | |
| " <td>2018.0</td>\n", | |
| " <td>51.0</td>\n", | |
| " <td>43</td>\n", | |
| " <td>43</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>5</th>\n", | |
| " <td>2018.0</td>\n", | |
| " <td>52.0</td>\n", | |
| " <td>41</td>\n", | |
| " <td>40</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>6</th>\n", | |
| " <td>2019.0</td>\n", | |
| " <td>1.0</td>\n", | |
| " <td>42</td>\n", | |
| " <td>39</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>7</th>\n", | |
| " <td>2019.0</td>\n", | |
| " <td>2.0</td>\n", | |
| " <td>45</td>\n", | |
| " <td>45</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>8</th>\n", | |
| " <td>2019.0</td>\n", | |
| " <td>3.0</td>\n", | |
| " <td>5</td>\n", | |
| " <td>5</td>\n", | |
| " </tr>\n", | |
| " <tr>\n", | |
| " <th>9</th>\n", | |
| " <td>2019.0</td>\n", | |
| " <td>4.0</td>\n", | |
| " <td>3</td>\n", | |
| " <td>2</td>\n", | |
| " </tr>\n", | |
| " </tbody>\n", | |
| "</table>\n", | |
| "</div>" | |
| ], | |
| "text/plain": [ | |
| " year week n_culver_devices n_la_devices_from_culver\n", | |
| "0 2018.0 1.0 3 3\n", | |
| "1 2018.0 48.0 21 21\n", | |
| "2 2018.0 49.0 38 35\n", | |
| "3 2018.0 50.0 40 40\n", | |
| "4 2018.0 51.0 43 43\n", | |
| "5 2018.0 52.0 41 40\n", | |
| "6 2019.0 1.0 42 39\n", | |
| "7 2019.0 2.0 45 45\n", | |
| "8 2019.0 3.0 5 5\n", | |
| "9 2019.0 4.0 3 2" | |
| ] | |
| }, | |
| "execution_count": 135, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ], | |
| "source": [ | |
| "query_res" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [] | |
| } | |
| ], | |
| "metadata": { | |
| "kernelspec": { | |
| "display_name": "Python 3", | |
| "language": "python", | |
| "name": "python3" | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 2 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment