kleong

rockset> select SOURCE,
            EXTRACT(DATE FROM PARSE_DATETIME_ISO8601(DATETIMEINIT)) as date
       from "oakland-call-center"
       where PARSE_DATETIME_ISO8601(DATETIMEINIT) > CURRENT_DATETIME() - DAYS(3)

kleong

rockset> describe "oakland-call-center";
+---------------------+---------------+---------+-----------+
| field               | occurrences   | total   | type      |
|---------------------+---------------+---------+-----------|
| ['BEAT']            | 608949        | 608949  | string    |
| ['COUNCILDISTRICT'] | 608949        | 608949  | string    |
| ['City']            | 608949        | 608949  | string    |
| ['DATETIMECLOSED']  | 608949        | 608949  | string    |
| ['DATETIMEINIT']    | 608949        | 608949  | string    |
| ['DESCRIPTION']     | 608949        | 608949  | string    |

kleong

rockset> DESCRIBE movie_ratings

+--------------------------------------------+---------------+---------+-----------+
| field                                      | occurrences   | total   | type      |
|--------------------------------------------+---------------+---------+-----------|
| ['12 Strong']                              | 1             | 3       | object    |
| ['12 Strong', 'Genre']                     | 1             | 1       | string    |
| ['12 Strong', 'Gross']                     | 1             | 1       | string    |
| ['12 Strong', 'IMDB Metascore']            | 1             | 1       | string    |
| ['12 Strong', 'Popcorn Score']             | 1             | 1       | int       |

kleong

{
    "12 Strong": {
        "Genre": "Action",
        "Gross": "$1,465,000",
        "IMDB Metascore": "54",
        "Popcorn Score": 72,
        "Rating": "R",
        "Tomato Score": 54
    },
    "A Ciambra": {

kleong

> with details as (
    select tokenize(REGEXP_EXTRACT(text, 'Statement Date: .*'))[3] as month,
    tokenize(REGEXP_EXTRACT(text, 'Statement Date: .*'))[5] as year,
    cast(tokenize(REGEXP_EXTRACT(text, 'Total Amount Due:\n.*\nAmount Enclosed'))[4] as float) as amount
    from "elec-bills"
) 
select concat(month, '/', year) as billing_period, amount
from details
order by year asc, month;

kleong

> describe "elec-bills";
+--------------------------------------------+---------------+---------+-----------+
| field                                      | occurrences   | total   | type      |
|--------------------------------------------+---------------+---------+-----------|
| ['Author']                                 | 9             | 9       | string    |
| ['CreationDate']                           | 9             | 9       | string    |
| ['Creator']                                | 9             | 9       | string    |
| ['ModDate']                                | 9             | 9       | string    |
| ['Producer']                               | 9             | 9       | string    |
| ['Subject']                                | 9             | 9       | string    |

kleong

rockset> SELECT
             user.user.name
         FROM
             twitter_collection AS col, 
             UNNEST(col.entities.user_mentions AS user) AS user
         WHERE
             typeof(user.user.name) = 'string'     
         GROUP BY
             user.user.name
         ORDER BY

kleong

rockset> SELECT      
             col.name
         FROM
             twitter_collection:entities.user_mentions AS col
         WHERE 
             typeof(col.name) = 'string'
         LIMIT 5;
+------------------------------------+
| name                               |
|------------------------------------|

kleong

rockset> DESCRIBE twitter_collection:entities.user_mentions;
+-----------------------+---------------+----------+-----------+
| field                 | occurrences   | total    | type      |
|-----------------------+---------------+----------+-----------|
| ['*']                 | 1531518       | 1531518  | object    |
| ['*', 'id']           | 329           | 1531518  | null_type |
| ['*', 'id']           | 1531189       | 1531518  | int       |
| ['*', 'id_str']       | 1531189       | 1531518  | string    |
| ['*', 'id_str']       | 329           | 1531518  | null_type |
| ['*', 'indices']      | 1531518       | 1531518  | array     |

kleong

rockset> SELECT 
             text AS hashtag
         FROM 
             twitter_collection:entities.hashtags AS hashtags
         WHERE 
             text IS NOT NULL 
         GROUP BY 
             text 
         ORDER BY  
             COUNT(*) DESC