CREATE INDEX (SAI)

CREATE CUSTOM INDEX IF NOT EXISTS race_year_idx
  ON cycling.rank_by_year_and_name (race_year) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX IF NOT EXISTS race_name_idx
  ON cycling.rank_by_year_and_name (race_name) USING 'StorageAttachedIndex';

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES

(3 rows)

CREATE CUSTOM INDEX IF NOT EXISTS rank_idx
ON cycling.rank_by_year_and_name (rank) USING 'StorageAttachedIndex';

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank = 3;

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |         Thomas LEBAS

(3 rows)

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3
ALLOW FILTERING;

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES

(2 rows)

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

CREATE CUSTOM INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality)
  USING 'StorageAttachedIndex';

SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE nationality = 'France';

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2017-04-16 | 1990-05-27

(1 rows)

CREATE CUSTOM INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday)
  USING 'StorageAttachedIndex';

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1981-03-29';

 lastname  | birthday   | nationality
-----------+------------+-------------
 VEIKKANEN | 1981-03-29 |     Finland

(1 rows)

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1991-08-25' AND nationality = 'Ethiopia'

 lastname | birthday   | nationality
----------+------------+-------------
    GRMAY | 1991-08-25 |    Ethiopia

(1 rows)

SELECT * FROM cycling.cyclist_alt_stats
  WHERE nationality IN ('Finland', 'France');

 id                                   | birthday   | first_race | height | last_race  | lastname  | nationality | w_units | weight
--------------------------------------+------------+------------+--------+------------+-----------+-------------+---------+--------
 c09e9451-50da-483d-8108-e6bea2e827b3 | 1981-03-29 | 1996-05-21 |   1.78 | 2012-10-02 | VEIKKANEN |     Finland |      kg |     66
 4ceb495c-55ab-4f71-83b9-81117252bb13 | 1990-05-27 | 2006-03-15 |   null | 2017-04-16 |     DUVAL |      France |    null |   null

(2 rows)

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday >'1981-03-29';

 lastname  | birthday   | nationality
-----------+------------+-------------
     BRUTT | 1982-01-29 |      Russia
  ISAYCHEV | 1986-04-21 |      Russia
 TSATEVICH | 1989-07-05 |      Russia
  PAULINHO | 1990-05-27 |    Portugal
     GRMAY | 1991-08-25 |    Ethiopia
    BELKOV | 1985-01-09 |      Russia
     DUVAL | 1990-05-27 |      France

(7 rows)

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1991-08-25' OR nationality = 'Ethiopia';

 lastname | birthday   | nationality
----------+------------+-------------
    GRMAY | 1991-08-25 |    Ethiopia

(1 rows)

CREATE TABLE IF NOT EXISTS cycling.comments (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);

CREATE CUSTOM INDEX created_at_idx ON cycling.comments (created_at)
  USING 'StorageAttachedIndex';

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

CREATE CUSTOM INDEX weight_idx ON cycling.cyclist_alt_stats (weight)
  USING 'StorageAttachedIndex';

CREATE TABLE IF NOT EXISTS cycling.nation_rank (
  nation text PRIMARY KEY,
  info tuple<int, text, int>
);

CREATE CUSTOM INDEX info_idx ON cycling.nation_rank (info) USING 'StorageAttachedIndex';

SELECT * FROM cycling.nation_rank
  WHERE info = (3, 'Phillippe GILBERT', 6222);

 nation  | info
---------+--------------------------------
 Belgium | (3, 'Phillippe GILBERT', 6222)

(1 rows)

CREATE TYPE IF NOT EXISTS cycling.race (
  race_title text,
  race_date timestamp,
  race_time text
);

CREATE TABLE IF NOT EXISTS cycling.cyclist_races (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text,
  races list<FROZEN <race>>
);

CREATE CUSTOM INDEX IF NOT EXISTS races_idx
  ON cycling.cyclist_races (races) USING 'StorageAttachedIndex';

SELECT * FROM cycling.cyclist_races
  WHERE races CONTAINS {
    race_title:'Rabobank 7-Dorpenomloop Aalburg',
    race_date:'2015-05-09',
    race_time:'02:58:33'};

 id                                   | firstname | lastname | races
--------------------------------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |      VOS | [race(race_title='Rabobank 7-Dorpenomloop Aalburg', race_date=datetime.datetime(2015, 5, 9, 0, 0), race_time='02:58:33'), race(race_title='Ronde van Gelderland', race_date=datetime.datetime(2015, 4, 19, 0, 0), race_time='03:22:23')]

(1 rows)