janbenetka

CREATE TABLE dataset.new_table
PARTITION BY DATE(timestamp_column) AS
SELECT x, y, z, timestamp_column
FROM dataset.existing_table

CREATE OR REPLACE TABLE `uc-prox-core-dev.14_days_retention.home_od_flux_2020_aggregated_condensed` 
PARTITION BY minProcessingDate
AS
SELECT...

janbenetka

import plotly.graph_objects as go

countries = increase_per_country.country

fig = go.Figure()
fig.add_trace(go.Bar(
    x=countries,
    y=increase_per_country.identifier_count_new,
    name='New data',
    marker_color='#FF8000'

janbenetka

import pandas as pd 
pd.options.display.max_columns = 50  # None -> No Restrictions
pd.options.display.max_rows = 200    # None -> Be careful with this 
pd.options.display.max_colwidth = 100
pd.options.display.precision = 3

janbenetka

df = pd.DataFrame(dict(A=[2,6,3],
                       B=[2,2,6], 
                       C=[3,2,3]))
df['col_total']     = df.apply(lambda x: x.sum(), axis=1)
df.loc['row_total'] = df.apply(lambda x: x.sum())

janbenetka

# number of counts per value
df['genre'].value_counts()

# fraction per value (sums up to 1.0)
df['genre'].value_counts(normalize=True)

janbenetka

AF 	 Afghanistan
AL 	 Albania
DZ 	 Algeria
AS 	 American Samoa
AD 	 Andorra
AO 	 Angola
AI 	 Anguilla
AQ 	 Antarctica
AG 	 Antigua and Barbuda
AR 	 Argentina

janbenetka

with per_os AS (
  SELECT  
    osType,
    fips,
    count(*) count,
    any_value(geog) geog
  FROM `uc-puzzle-data.clusters_dwh.cluster_US` clusters 
       , `uc-atlas.maps_us.census_tracts` tract
  WHERE
    localEventDate BETWEEN "2020-01-15" AND "2020-01-30"

janbenetka

  with count_per_identifier AS (
    SELECT 
        identifier, 
        COUNT(DISTINCT home_area_id) home_count
    FROM `uc-prox-core-dev.30_day_retention.home_identifier_weekly_8w_20d_100h`
    GROUP BY identifier
  )
  SELECT home_count, SUM(count) count FROM (SELECT 1 as count, home_count FROM count_per_identifier)
  GROUP BY home_count
  ORDER BY home_count

janbenetka

import plotly.express as px
df = px.data.iris()
fig = px.parallel_coordinates(df, color="species_id",
                              dimensions=['sepal_width', 'sepal_length', 'petal_width',
                                          'petal_length'],
                              color_continuous_scale=px.colors.diverging.Tealrose,
                              color_continuous_midpoint=2)
fig.show()

#-----------------------------------

janbenetka

SELECT url, COUNT(*) AS popularity, GROUP_CONCAT(tweet) 
FROM Table GROUP BY url ORDER BY popularity