### Define and select PROTO array data

Source: https://github.com/google/googlesql/blob/master/docs/arrays.md

This example demonstrates how to define a table with an ARRAY of PROTOs and select all its contents. It serves as a setup for further querying.

```googlesql
WITH
  Albums AS (
  SELECT
    'Let It Be' AS album_name,
    [
      NEW googlesql.examples.music.Chart(1 AS rank, 'US 100' AS chart_name),
      NEW googlesql.examples.music.Chart(1 AS rank, 'UK 40' AS chart_name),
      NEW googlesql.examples.music.Chart(2 AS rank, 'Oricon' AS chart_name)
    ] AS charts
  UNION ALL
  SELECT
    'Rubber Soul' AS album_name,
    [
      NEW googlesql.examples.music.Chart(1 AS rank, 'US 100' AS chart_name),
      NEW googlesql.examples.music.Chart(1 AS rank, 'UK 40' AS chart_name),
      NEW googlesql.examples.music.Chart(24 AS rank, 'Oricon' AS chart_name)
    ] AS charts
  )
SELECT * 
FROM Albums;
```

--------------------------------

### Example Data Setup for SalesTable

Source: https://github.com/google/googlesql/blob/master/docs/operators.md

This CTE defines a sample SalesTable with nested structures and arrays, used in subsequent examples for demonstrating array element field access.

```googlesql
WITH
  SalesTable AS (
    SELECT
      [
        STRUCT(
          [
            STRUCT([25.0, 75.0] AS prices),
            STRUCT([50.0, 100.0] AS prices)
          ] AS sales
        )
      ] AS my_array
  )
```

--------------------------------

### BIT_OR Example

Source: https://github.com/google/googlesql/blob/master/docs/aggregate_functions.md

Example demonstrating the BIT_OR function with a sample dataset.

```googlesql
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

/*--------+
 | bit_or |
 +--------+
 | 61601  |
 +--------*/
```

--------------------------------

### Argument Examples

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

Examples of valid input formats for the function arguments.

```googlesql
'["apples", "oranges", "grapes"]'
```

```googlesql
JSON '["apples", "oranges", "grapes"]'
```

--------------------------------

### LIMIT and OFFSET Examples

Source: https://github.com/google/googlesql/blob/master/docs/query-syntax.md

Examples demonstrating row limitation and offset application.

```googlesql
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2;
```

```googlesql
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1;
```

--------------------------------

### MIN Function Examples

Source: https://github.com/google/googlesql/blob/master/docs/aggregate_functions.md

Examples demonstrating basic aggregation and window function usage with MIN.

```googlesql
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

/*-----+
 | min |
 +-----+
 | 4   |
 +-----*/
```

```googlesql
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

/*------+------+
 | x    | min  |
 +------+------+
 | NULL | NULL |
 | NULL | NULL |
 | 8    | 4    |
 | 4    | 4    |
 | 37   | 37   |
 | 55   | 37   |
 +------+------*/
```

--------------------------------

### BIT_XOR Example 1

Source: https://github.com/google/googlesql/blob/master/docs/aggregate_functions.md

Example demonstrating the BIT_XOR function with a sample dataset.

```googlesql
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

/*---------+
 | bit_xor |
 +---------+
 | 4860    |
 +---------*/
```

--------------------------------

### MAX Function Examples

Source: https://github.com/google/googlesql/blob/master/docs/aggregate_functions.md

Examples demonstrating basic aggregation and window function usage with MAX.

```googlesql
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 55, 4]) AS x;

/*-----+
 | max |
 +-----+
 | 55  |
 +-----*/
```

```googlesql
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 55, NULL, 4]) AS x;

/*------+------+
 | x    | max  |
 +------+------+
 | NULL | NULL |
 | NULL | NULL |
 | 8    | 8    |
 | 4    | 8    |
 | 37   | 55   |
 | 55   | 55   |
 +------+------*/
```

--------------------------------

### COUNT Examples

Source: https://github.com/google/googlesql/blob/master/docs/aggregate_functions.md

Various examples demonstrating the use of COUNT with aggregation, window functions, and conditional logic.

```googlesql
SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

/*------------+--------------+
 | count_star | count_dist_x |
 +------------+--------------+
 | 4          | 3            |
 +------------+--------------*/
```

```googlesql
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

/*------+------------+--------------+
 | x    | count_star | count_dist_x |
 +------+------------+--------------+
 | 1    | 3          | 2            |
 | 4    | 3          | 2            |
 | 4    | 3          | 2            |
 | 5    | 1          | 1            |
 +------+------------+--------------*/
```

```googlesql
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

/*------+------------+---------+
 | x    | count_star | count_x |
 +------+------------+---------+
 | NULL | 1          | 0       |
 | 1    | 3          | 3       |
 | 4    | 3          | 3       |
 | 4    | 3          | 3       |
 | 5    | 1          | 1       |
 +------+------------+---------*/
```

```googlesql
SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

/*-------------------+
 | distinct_positive |
 +-------------------+
 | 3                 |
 +-------------------*/
```

```googlesql
WITH Events AS (
```

--------------------------------

### Example JSON input

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

A simple example of a JSON array input.

```googlesql
JSON '[9.8]'
```

--------------------------------

### HAVING MIN usage examples

Source: https://github.com/google/googlesql/blob/master/docs/aggregate-function-calls.md

Examples demonstrating the use of HAVING MIN with ANY_VALUE and AVG aggregation functions.

```googlesql
WITH
  Precipitation AS (
    SELECT 2009 AS year, 'spring' AS season, 3 AS inches
    UNION ALL
    SELECT 2001, 'winter', 4
    UNION ALL
    SELECT 2003, 'fall', 1
    UNION ALL
    SELECT 2002, 'spring', 4
    UNION ALL
    SELECT 2005, 'summer', 1
  )
SELECT ANY_VALUE(year HAVING MIN inches) AS any_year_with_min_inches FROM Precipitation;
```

```googlesql
WITH
  Precipitation AS (
    SELECT 2001 AS year, 'spring' AS season, 9 AS inches
    UNION ALL
    SELECT 2001, 'winter', 1
    UNION ALL
    SELECT 2000, 'fall', 3
    UNION ALL
    SELECT 2000, 'summer', 5
    UNION ALL
    SELECT 2000, 'spring', 7
    UNION ALL
    SELECT 2000, 'winter', 2
  )
SELECT AVG(inches HAVING MIN year) AS average FROM Precipitation;
```

--------------------------------

### BIT_XOR Example 2

Source: https://github.com/google/googlesql/blob/master/docs/aggregate_functions.md

Example demonstrating the BIT_XOR function with a repeated value in the dataset.

```googlesql
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

/*---------+
 | bit_xor |
 +---------+
 | 5678    |
 +---------*/
```

--------------------------------

### IF statement example

Source: https://github.com/google/googlesql/blob/master/docs/procedural-language.md

Example demonstrating conditional logic to check for product existence in tables.

```sql
DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS(SELECT 1 FROM schema.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
  ELSEIF EXISTS(SELECT 1 FROM schema.more_products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product from more_products table',
  CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;
```

--------------------------------

### Table Name Examples

Source: https://github.com/google/googlesql/blob/master/docs/lexical.md

Provides examples of valid table names, including unquoted and quoted identifiers.

```none
mytable
```

```none
`287mytable`
```

--------------------------------

### ANON_SUM Examples

Source: https://github.com/google/googlesql/blob/master/docs/aggregate-dp-functions.md

Examples demonstrating the use of ANON_SUM with and without noise.

```googlesql
-- With noise, using the epsilon parameter.
SELECT
  WITH ANONYMIZATION
    OPTIONS(epsilon=10, delta=.01, max_groups_contributed=1)
    item,
    ANON_SUM(quantity CLAMPED BETWEEN 0 AND 100) quantity
FROM {{USERNAME}}.view_on_professors
GROUP BY item;

-- These results will change each time you run the query.
-- Smaller aggregations might be removed.
/*----------+-----------+
 | item     | quantity  |
 +----------+-----------+
 | pencil   | 143       |
 | pen      | 59        |
 +----------+-----------*/
```

```googlesql
-- Without noise, using the epsilon parameter.
-- (this un-noised version is for demonstration only)
SELECT
  WITH ANONYMIZATION
    OPTIONS(epsilon=1e20, delta=.01, max_groups_contributed=1)
    item,
    ANON_SUM(quantity) quantity
FROM {{USERNAME}}.view_on_professors
GROUP BY item;

-- These results will not change when you run the query.
/*----------+----------+
 | item     | quantity |
 +----------+----------+
 | scissors | 8        |
 | pencil   | 144      |
 | pen      | 58       |
 +----------+----------*/
```

--------------------------------

### PARSE_JSON Example Input

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

Example of a JSON-formatted string input.

```googlesql
'{"class": {"students": [{"name": "Jane"}]}}'
```

--------------------------------

### JSON Example Input

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

Example of a JSON input expression.

```googlesql
JSON '999'
```

--------------------------------

### JSON_QUERY_ARRAY Argument Examples

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

Examples of valid input types for the function.

```googlesql
'["a", "b", {"key": "c"}]'
```

```googlesql
JSON '["a", "b", {"key": "c"}]'
```

--------------------------------

### JSON Example Input

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

Example of a JSON array input for the function.

```googlesql
JSON '[999, 12]'
```

--------------------------------

### PARSE_TIMESTAMP Usage Examples

Source: https://github.com/google/googlesql/blob/master/docs/timestamp_functions.md

Examples demonstrating how format elements must match the input string structure.

```googlesql
-- This works because elements on both sides match.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008");

-- This produces an error because the year element is in different locations.
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008");

-- This produces an error because one of the year elements is missing.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008");

-- This works because %c can find all matching elements in timestamp_string.
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008");
```

--------------------------------

### ARRAY_SLICE Example: Negative Start Offset

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Shows how ARRAY_SLICE handles a negative start offset combined with a positive end offset. In this case, the result is an empty array because the effective start is after the end.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -1, 3) AS result
```

--------------------------------

### Sample Data Initialization

Source: https://github.com/google/googlesql/blob/master/docs/protocol_buffer_functions.md

Initializes a sample table 'AlbumList' with Album and Chart proto data for use in examples.

```googlesql
WITH AlbumList AS (
  SELECT
    NEW Album(
      'Alana Yah' AS solo,
      'New Moon' AS album_name,
      ['Sandstorm','Wait'] AS song) AS album_col,
    NEW Chart(
      'Billboard' AS chart_name,
      '2016-04-23' AS date,
      1 AS rank) AS chart_col
    UNION ALL
  SELECT
    NEW Album(
      'The Roadlands' AS band,
      'Grit' AS album_name,
      ['The Way', 'Awake', 'Lost Things'] AS song) AS album_col,
    NEW Chart(
      'Billboard' AS chart_name,
      1 as rank) AS chart_col
)
SELECT * FROM AlbumList
```

--------------------------------

### IS DISTINCT FROM Examples Returning TRUE

Source: https://github.com/google/googlesql/blob/master/docs/operators.md

Examples of expressions that evaluate to TRUE.

```googlesql
SELECT 1 IS DISTINCT FROM 2
```

```googlesql
SELECT 1 IS DISTINCT FROM NULL
```

```googlesql
SELECT 1 IS NOT DISTINCT FROM 1
```

```googlesql
SELECT NULL IS NOT DISTINCT FROM NULL
```

--------------------------------

### HAVING MAX usage examples

Source: https://github.com/google/googlesql/blob/master/docs/aggregate-function-calls.md

Examples demonstrating the use of HAVING MAX with ANY_VALUE and AVG aggregation functions.

```googlesql
WITH
  Precipitation AS (
    SELECT 2009 AS year, 'spring' AS season, 3 AS inches
    UNION ALL
    SELECT 2001, 'winter', 4
    UNION ALL
    SELECT 2003, 'fall', 1
    UNION ALL
    SELECT 2002, 'spring', 4
    UNION ALL
    SELECT 2005, 'summer', 1
  )
SELECT ANY_VALUE(year HAVING MAX inches) AS any_year_with_max_inches FROM Precipitation;
```

```googlesql
WITH
  Precipitation AS (
    SELECT 2001 AS year, 'spring' AS season, 9 AS inches
    UNION ALL
    SELECT 2001, 'winter', 1
    UNION ALL
    SELECT 2000, 'fall', 3
    UNION ALL
    SELECT 2000, 'summer', 5
    UNION ALL
    SELECT 2000, 'spring', 7
    UNION ALL
    SELECT 2000, 'winter', 2
  )
SELECT AVG(inches HAVING MAX year) AS average FROM Precipitation;
```

--------------------------------

### NTILE Usage Example

Source: https://github.com/google/googlesql/blob/master/docs/numbering_functions.md

Demonstrates partitioning finishers by division and ranking them into 3 buckets using NTILE.

```googlesql
WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
  finish_time,
  division,
  NTILE(3) OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
```

--------------------------------

### ARRAY_SLICE Example: Positive Start (Out-of-Bounds), Positive End (Out-of-Bounds)

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Demonstrates slicing with a positive start offset that is out of bounds. This results in an empty array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 5, 30) AS result
```

--------------------------------

### Create a database with options

Source: https://github.com/google/googlesql/blob/master/docs/data-definition-language.md

Defines a new database with specific configuration options using the HINT syntax.

```googlesql
CREATE DATABASE library OPTIONS(
  base_dir=`/city/orgs`,
  owner='libadmin'
);

/*--------------------+
 | Database           |
 +--------------------+
 | library            |
 +--------------------*/
```

--------------------------------

### ARRAY_SLICE Example: Negative Start (Out-of-Bounds), Negative End (Out-of-Bounds)

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Illustrates slicing with both negative offsets that are out of bounds. The slice starts from the beginning of the array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -30, -5) AS result
```

--------------------------------

### FLOAT_ARRAY Usage Examples

Source: https://github.com/google/googlesql/blob/master/docs/json_functions.md

Examples demonstrating basic usage and wide_number_mode configuration for FLOAT_ARRAY.

```googlesql
JSON '[9.8]'
```

```googlesql
SELECT FLOAT_ARRAY(JSON '[9, 9.8]') AS velocities;
```

```googlesql
SELECT FLOAT_ARRAY(JSON '[16777217]', wide_number_mode=>'round') as result;
```

```googlesql
SELECT FLOAT_ARRAY(JSON '[16777216]') as result;
```

--------------------------------

### Launch Web UI for Interactive Querying

Source: https://github.com/google/googlesql/blob/master/execute_query.md

Start a local web server to interactively enter and execute queries.

```sh
execute_query --web
```

--------------------------------

### ST_LINESUBSTRING

Source: https://github.com/google/googlesql/blob/master/docs/geography_functions.md

Gets a segment of a single linestring at a specific starting and ending fraction.

```APIDOC
## ST_LINESUBSTRING

### Description
Gets a segment of a single linestring at a specific starting and ending fraction.
```

--------------------------------

### PARSE_DATE Format Examples

Source: https://github.com/google/googlesql/blob/master/docs/date_functions.md

Examples converting specific date string formats to DATE objects.

```googlesql
SELECT PARSE_DATE('%x', '12/25/08') AS parsed;

/*------------+
 | parsed     |
 +------------+
 | 2008-12-25 |
 +------------*/
```

```googlesql
SELECT PARSE_DATE('%Y%m%d', '20081225') AS parsed;

/*------------+
 | parsed     |
 +------------+
 | 2008-12-25 |
 +------------*/
```

--------------------------------

### ARRAY_SLICE Example: Positive Start, Negative Out-of-Bounds End

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Demonstrates slicing with a positive start offset and a negative end offset that is out of bounds. This results in an empty array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, -30) AS result
```

--------------------------------

### Example: Creating a Planet Protocol Buffer

Source: https://github.com/google/googlesql/blob/master/docs/data-types.md

Demonstrates creating a complex protocol buffer for a 'Planet' object, including nested messages for facts, arrays for moons, and SQL expressions for values.

```googlesql
NEW googlesql.examples.astronomy.Planet {
  planet_name: 'Jupiter'
  facts: {
    length_of_day: 9.93
    distance_to_sun: 5.2 * ASTRONOMICAL_UNIT
    has_rings: TRUE
  }
  major_moons: [
    { moon_name: 'Io' },
    { moon_name: 'Europa' },
    { moon_name: 'Ganymede' },
    { moon_name: 'Callisto'}
  ]
  minor_moons: (
    SELECT ARRAY_AGG(moon_name)
    FROM SolarSystemMoons
    WHERE
      planet_name = 'Jupiter'
      AND circumference < 3121
  )
  count_of_space_probe_photos: (
    GALILEO_PHOTOS
    + JUNO_PHOTOS
    + NEW_HORIZONS_PHOTOS
    + CASSINI_PHOTOS
    + ULYSSES_PHOTOS
    + VOYAGER_1_PHOTOS
    + VOYAGER_2_PHOTOS
    + PIONEER_10_PHOTOS
    + PIONEER_11_PHOTOS
  ),
  (UniverseExtraInfo.extension) {
    ...
  }
}
```

--------------------------------

### ARRAY_SLICE Example: Positive Start, Out-of-Bounds End

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Illustrates slicing with a positive start offset and an end offset that exceeds the array bounds. The slice extends to the end of the array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, 30) AS result
```

--------------------------------

### ARRAY_SLICE Example: Positive Start, Negative End

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Illustrates slicing with a positive start offset and a negative end offset. The negative end offset is calculated from the end of the array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, -3) AS result
```

--------------------------------

### ST_LINELOCATEPOINT

Source: https://github.com/google/googlesql/blob/master/docs/geography_functions.md

Gets a section of a linestring GEOGRAPHY value between the start point and a point GEOGRAPHY value.

```APIDOC
## ST_LINELOCATEPOINT

### Description
Gets a section of a linestring GEOGRAPHY value between the start point and a point GEOGRAPHY value.
```

--------------------------------

### Named Window Usage Rules

Source: https://github.com/google/googlesql/blob/master/docs/window-function-calls.md

Examples showing valid and invalid ways to combine named windows with additional specifications.

```googlesql
--this works:
SELECT item, purchases, LAST_VALUE(item)
  OVER (ItemWindow ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW ItemWindow AS (ORDER BY purchases)

--this doesn't work:
SELECT item, purchases, LAST_VALUE(item)
  OVER (ItemWindow ORDER BY purchases) AS most_popular
FROM Produce
WINDOW ItemWindow AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
```

--------------------------------

### Get Last Day of Week (Monday start)

Source: https://github.com/google/googlesql/blob/master/docs/datetime_functions.md

Returns the last day of the week for a given DATETIME expression, where the week is defined as starting on Monday. The WEEK(MONDAY) date part is specified.

```googlesql
SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(MONDAY)) AS last_day

/*------------+
 | last_day   |
 +------------+
 | 2008-11-16 |
 +------------*/
```

--------------------------------

### Get Last Day of Week (Sunday start)

Source: https://github.com/google/googlesql/blob/master/docs/datetime_functions.md

Returns the last day of the week for a given DATETIME expression, where the week is defined as starting on Sunday. The WEEK(SUNDAY) date part is specified.

```googlesql
SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(SUNDAY)) AS last_day

/*------------+
 | last_day   |
 +------------+
 | 2008-11-15 |
 +------------*/
```

--------------------------------

### ST_LINELOCATEPOINT

Source: https://github.com/google/googlesql/blob/master/docs/geography_functions.md

Gets a section of a linestring between the start point and a selected point, returning the percentage this section represents in the linestring.

```APIDOC
## ST_LINELOCATEPOINT

### Description
Gets a section of a linestring between the start point and a selected point (a point on the linestring closest to the `point_geography` argument). Returns the percentage that this section represents in the linestring.

### Method
N/A (This is a SQL function, not an API endpoint)

### Endpoint
N/A

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Parameters
- **linestring_geography** (GEOGRAPHY) - Required - A linestring GEOGRAPHY.
- **point_geography** (GEOGRAPHY) - Required - A point GEOGRAPHY.

### Request Example
```googlesql
WITH geos AS (
    SELECT ST_GEOGPOINT(0, 0) AS point UNION ALL
    SELECT ST_GEOGPOINT(1, 0) UNION ALL
    SELECT ST_GEOGPOINT(1, 1) UNION ALL
    SELECT ST_GEOGPOINT(2, 2) UNION ALL
    SELECT ST_GEOGPOINT(3, 3) UNION ALL
    SELECT ST_GEOGPOINT(4, 4) UNION ALL
    SELECT ST_GEOGPOINT(5, 5) UNION ALL
    SELECT ST_GEOGPOINT(6, 5) UNION ALL
    SELECT NULL
  )
SELECT
  point AS input_point,
  ST_LINELOCATEPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 5 5)'), point)
    AS percentage_from_beginning
FROM geos
```

### Response
#### Success Response (DOUBLE)
- **percentage_from_beginning** (DOUBLE) - The percentage (0-1) that the section from the start of the linestring to the selected point represents.

#### Response Example
```googlesql
/*-------------+---------------------------+
 | input_point | percentage_from_beginning |
 +-------------+---------------------------+
 | POINT(0 0)  | 0                         |
 | POINT(1 0)  | 0                         |
 | POINT(1 1)  | 0                         |
 | POINT(2 2)  | 0.25015214685147907       |
 | POINT(3 3)  | 0.5002284283637185        |
 | POINT(4 4)  | 0.7501905913884388        |
 | POINT(5 5)  | 1                         |
 | POINT(6 5)  | 1                         |
 | NULL        | NULL                      |
 +-------------+---------------------------*/
```

### Details
- To select a point on the linestring `GEOGRAPHY` (`linestring_geography`), this function takes a point `GEOGRAPHY` (`point_geography`) and finds the closest point to it on the linestring.
- If two points on `linestring_geography` are an equal distance away from `point_geography`, it isn't guaranteed which one will be selected.
- The return value is an inclusive value between 0 and 1 (0-100%).
- If the selected point is the start point on the linestring, function returns 0 (0%).
- If the selected point is the end point on the linestring, function returns 1 (100%).
- Returns `NULL` if any input argument is `NULL`.
- Returns an error if `linestring_geography` isn't a linestring or if `point_geography` isn't a point. Use the `SAFE` prefix to obtain `NULL` for invalid input instead of an error.
```

--------------------------------

### Invalid Table Name Examples

Source: https://github.com/google/googlesql/blob/master/docs/lexical.md

Shows invalid table names due to starting with a number or containing invalid characters after a dash.

```googlesql
-- Invalid table name. The table name starts with a number and is
-- unquoted.
287mytable
```

```googlesql
-- Invalid table name. The table name is unquoted and isn't a valid
-- dashed identifier, as the part after the dash is neither a number nor
-- an identifier starting with a letter or an underscore.
mytable-287a
```

--------------------------------

### ARRAY_SLICE Example: Single Element Slice (Positive Offset)

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Extracts a single element using identical positive start and end offsets.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 3, 3) AS result
```

--------------------------------

### Create Protocol Buffer with NEW (Parenthesized Arguments Example)

Source: https://github.com/google/googlesql/blob/master/docs/operators.md

Example showing how to use the NEW operator with a parenthesized list of arguments to create a Chart protocol buffer, aliasing fields for clarity.

```googlesql
SELECT
  key,
  name,
  NEW googlesql.examples.music.Chart(key AS rank, name AS chart_name)
FROM
  (SELECT 1 AS key, "2" AS name);
```

--------------------------------

### ARRAY_SLICE Example: Both Negative Offsets

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Demonstrates using negative offsets for both start and end. The slice is taken from the end of the array towards the beginning.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -1, -3) AS result
```

--------------------------------

### UNION DISTINCT Example

Source: https://github.com/google/googlesql/blob/master/docs/query-syntax.md

Combines results from two queries, removing duplicate rows. Use to get a unique set of rows from both queries.

```googlesql
SELECT * FROM UNNEST(ARRAY<INT64>[1, 2, 3]) AS number
UNION DISTINCT
SELECT 1;

/*--------+
 | number |
 +--------+
 | 1      |
 | 2      |
 | 3      |
 +--------*/
```

--------------------------------

### Create Protocol Buffer with NEW (Map Constructor Example)

Source: https://github.com/google/googlesql/blob/master/docs/operators.md

Example demonstrating the creation of a Universe protocol buffer using the NEW operator with a map constructor, including nested fields and repeated fields.

```googlesql
NEW Universe {
  name: "Sol"
  closest_planets: ["Mercury", "Venus", "Earth" ]
  star {
    radius_miles: 432,690
    age: 4,603,000,000
  }
  constellations: [{
    name: "Libra"
    index: 0
  }, {
    name: "Scorpio"
    index: 1
  }]
  all_planets: (SELECT planets FROM SolTable)
}
```

--------------------------------

### BEGIN TRANSACTION example

Source: https://github.com/google/googlesql/blob/master/docs/procedural-language.md

Performs a series of operations including temporary table creation, deletion, and merging within a transaction.

```sql
BEGIN TRANSACTION;

-- Create a temporary table of new arrivals from warehouse #1
CREATE TEMP TABLE tmp AS
SELECT * FROM myschema.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the original table.
DELETE myschema.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the matching records into the Inventory table.
MERGE myschema.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

DROP TABLE tmp;

COMMIT TRANSACTION;
```

--------------------------------

### ARRAY_SLICE Example: Negative Start (Out-of-Bounds), Positive End (Out-of-Bounds)

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Shows slicing with both negative and positive offsets that are out of bounds. The entire array is returned.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -30, 30) AS result
```

--------------------------------

### PARSE_DATE Usage Examples

Source: https://github.com/google/googlesql/blob/master/docs/date_functions.md

Examples demonstrating successful parsing and common error scenarios based on format string matching.

```googlesql
-- This works because elements on both sides match.
SELECT PARSE_DATE('%A %b %e %Y', 'Thursday Dec 25 2008');

-- This produces an error because the year element is in different locations.
SELECT PARSE_DATE('%Y %A %b %e', 'Thursday Dec 25 2008');

-- This produces an error because one of the year elements is missing.
SELECT PARSE_DATE('%A %b %e', 'Thursday Dec 25 2008');

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE('%F', '2000-12-30');
```

--------------------------------

### Applying Engine-Specific Hints

Source: https://github.com/google/googlesql/blob/master/docs/lexical.md

Demonstrates how to apply hints to modify query execution strategy. This example shows assigning different literal values to the 'file_count' hint for two different database engines.

```googlesql
@{ database_engine_a.file_count=23, database_engine_b.file_count=10 }
```

--------------------------------

### Generate Date Array Using Non-Constant Dates

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

This example demonstrates generating date arrays where the start and end dates are derived from table columns or subqueries.

```googlesql
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

/*--------------------------------------------------------------+
 | date_range                                                   |
 +--------------------------------------------------------------+
 | [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
 | [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
 | [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
 | [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
 +--------------------------------------------------------------*/
```

--------------------------------

### ARRAY_SLICE Example: Single Element Slice (Negative Offset)

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Extracts a single element using identical negative start and end offsets, calculated from the end of the array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -3, -3) AS result
```

--------------------------------

### Build and run execute_query with Bazel

Source: https://github.com/google/googlesql/blob/master/README.md

Build and immediately run the execute_query tool using Bazel. The '--web' flag is passed as an argument to the tool.

```bash
# Build and run the execute_query tool.
bazel run //googlesql/tools/execute_query:execute_query -- --web
```

--------------------------------

### WALK Path Mode Example

Source: https://github.com/google/googlesql/blob/master/docs/graph-patterns.md

Demonstrates the WALK path mode on a non-quantified path pattern. The first path in the results uses the same edge for t1 and t3.

```googlesql
GRAPH FinGraph
MATCH
  WALK (a1:Account)-[t1:Transfers]->(a2:Account)-[t2:Transfers]->
  (a3:Account)-[t3:Transfers]->(a4:Account)
WHERE a1.id < a4.id
RETURN
  t1.id as transfer1_id, t2.id as transfer2_id, t3.id as transfer3_id

/*--------------+--------------+--------------+
 | transfer1_id | transfer2_id | transfer3_id |
 +--------------+--------------+--------------+
 | 16           | 20           | 16           |
 | 7            | 16           | 20           |
 | 7            | 16           | 20           |
 +--------------+--------------+--------------*/
```

--------------------------------

### ARRAY_SLICE Example: Negative Start, Negative End (Valid Range)

Source: https://github.com/google/googlesql/blob/master/docs/array_functions.md

Shows a valid slice using two negative offsets, extracting elements from the end of the array.

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -3, -1) AS result
```

--------------------------------

### Equivalent Window Queries

Source: https://github.com/google/googlesql/blob/master/docs/window-function-calls.md

Demonstrates how an implicit default window frame is equivalent to an explicit one.

```googlesql
SELECT book, LAST_VALUE(book)
  OVER (ORDER BY year)
FROM Library
```

```googlesql
SELECT book, LAST_VALUE(book)
  OVER (
    ORDER BY year
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library
```

--------------------------------

### Get Current Time in GoogleSQL

Source: https://github.com/google/googlesql/blob/master/docs/time_functions.md

Returns the current time as a TIME object. Parentheses are optional. The time value is set at the start of the query and remains consistent throughout.

```googlesql
CURRENT_TIME([time_zone])
```

```googlesql
CURRENT_TIME
```

```googlesql
SELECT CURRENT_TIME() as now;

/*----------------------------+
 | now                        |
 +----------------------------+
 | 15:31:38.776361            |
 +----------------------------*/
```

--------------------------------

### Get previous results with multiple named windows

Source: https://github.com/google/googlesql/blob/master/docs/window-function-calls.md

Achieve previous results by defining multiple named windows with sequential logic. This example demonstrates chaining window specifications.

```googlesql
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (ItemWindow) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  ItemWindow AS (c)
```

--------------------------------

### VAR_POP Usage Examples

Source: https://github.com/google/googlesql/blob/master/docs/statistical_aggregate_functions.md

Various examples demonstrating VAR_POP behavior with different input sets, including NULLs and infinity.

```googlesql
SELECT VAR_POP(x) AS results FROM UNNEST([10, 14, 18]) AS x

/*--------------------+
 | results            |
 +--------------------+
 | 10.666666666666666 |
 +--------------------*/
```

```googlesql
SELECT VAR_POP(x) AS results FROM UNNEST([10, 14, NULL]) AS x

/*----------+
 | results |
 +---------+
 | 4       |
 +---------*/
```

```googlesql
SELECT VAR_POP(x) AS results FROM UNNEST([10, NULL]) AS x

/*----------+
 | results |
 +---------+
 | 0       |
 +---------*/
```

```googlesql
SELECT VAR_POP(x) AS results FROM UNNEST([NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/
```

```googlesql
SELECT VAR_POP(x) AS results FROM UNNEST([10, 14, CAST('Infinity' as DOUBLE)]) AS x

/*---------+
 | results |
 +---------+
 | NaN     |
 +---------*/
```

--------------------------------

### Get Current Date in GoogleSQL

Source: https://github.com/google/googlesql/blob/master/docs/date_functions.md

Returns the current date as a DATE object. Parentheses are optional when called with no arguments. The date value is set at the start of the query and remains consistent throughout.

```googlesql
CURRENT_DATE()
```

```googlesql
CURRENT_DATE(time_zone_expression)
```

```googlesql
CURRENT_DATE
```

```googlesql
SELECT CURRENT_DATE() AS the_date;
```

```googlesql
SELECT CURRENT_DATE('America/Los_Angeles') AS the_date;
```

```googlesql
SELECT CURRENT_DATE('-08') AS the_date;
```

```googlesql
SELECT CURRENT_DATE AS the_date;
```

--------------------------------

### Get Current Timestamp

Source: https://github.com/google/googlesql/blob/master/docs/timestamp_functions.md

Returns the current date and time as a timestamp object. The timestamp is continuous and non-ambiguous. Parentheses are optional. The value is set at the start of the query and remains consistent for all invocations within that query.

```googlesql
CURRENT_TIMESTAMP()
```

```googlesql
CURRENT_TIMESTAMP
```

```googlesql
SELECT CURRENT_TIMESTAMP() AS now;

/*---------------------------------------------+
 | now                                         |
 +---------------------------------------------+
 | 2020-06-02 17:00:53.110 America/Los_Angeles |
 +---------------------------------------------*/
```

--------------------------------

### Create and populate a temporary table using EXECUTE IMMEDIATE

Source: https://github.com/google/googlesql/blob/master/docs/procedural-language.md

Demonstrates creating a temporary table, inserting static values, inserting values using variables and positional placeholders, inserting values using named placeholders, and inserting values using concatenated expressions. Results can be stored using the INTO clause.

```googlesql
-- Create some variables.
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure).
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- Add a row for Ulysses, using the variables declared and the ? placeholder.
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- Add a row for Emma, using the identifier placeholder.
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- Add a row for Middlemarch, using an expression.
EXECUTE IMMEDIATE
  CONCAT(
    "INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)"
  );

-- The table looks similar to the following:
/*------------------+------------------+
 | title            | publish_date     |
 +------------------+------------------+
 | Hamlet           | 1599             |
 | Ulysses          | 1922             |
 | Emma             | 1815             |
 | Middlemarch      | 1871             |
 +------------------+------------------*/

-- Save the publish date of the earliest book, Hamlet, to a variable called
-- first_date.
EXECUTE IMMEDIATE "SELECT MIN(publish_date) FROM Books LIMIT 1" INTO first_date;
```

--------------------------------

### Compute Rank with RANK() Window Function

Source: https://github.com/google/googlesql/blob/master/docs/window-function-calls.md

Use the RANK() window function to calculate the rank of each record within its partition, ordered by a specified column. This example ranks employees by their start date within each department.

```googlesql
SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;
```

--------------------------------

### Equivalent standard syntax using temporary tables

Source: https://github.com/google/googlesql/blob/master/docs/pipe-syntax.md

Demonstrates the manual approach to achieving multiple outputs without the FORK operator.

```googlesql
-- Equivalent query using temporary tables in standard syntax.

-- Statement 0: Create a common table.
CREATE TEMP TABLE Fruit
SELECT 'apple' AS item, 100 AS sales
UNION ALL
SELECT 'banana' AS item, 150 AS sales;

-- Statement 1: Get the total count.
SELECT AGGREGATE COUNT(*) AS total_fruits
FROM Fruit;

-- Statement 2: Get the count of each fruit item.
SELECT AGGREGATE COUNT(*) AS count_by_item
GROUP BY item
FROM Fruit;

-- Statement 3: Get the raw data.
SELECT item, sales
FROM Fruit;
```

--------------------------------

### Get Datetime Bucket Lower Bound with Default Origin

Source: https://github.com/google/googlesql/blob/master/docs/time-series-functions.md

This example demonstrates using DATETIME_BUCKET with the default origin of '1950-01-01 00:00:00'. It calculates the lower bound for various datetimes using a 12-hour bucket width.

```googlesql
WITH some_datetimes AS (
  SELECT DATETIME '1949-12-30 13:00:00' AS my_datetime UNION ALL
  SELECT DATETIME '1949-12-31 00:00:00' UNION ALL
  SELECT DATETIME '1949-12-31 13:00:00' UNION ALL
  SELECT DATETIME '1950-01-01 00:00:00' UNION ALL
  SELECT DATETIME '1950-01-01 13:00:00' UNION ALL
  SELECT DATETIME '1950-01-02 00:00:00'
)
SELECT DATETIME_BUCKET(my_datetime, INTERVAL 12 HOUR) AS bucket_lower_bound
FROM some_datetimes;
```

--------------------------------

### Create Table with Schema Options and Foreign Key

Source: https://github.com/google/googlesql/blob/master/docs/data-definition-language.md

Example of creating a 'books' table with schema options for column descriptions and deprecation status, and a foreign key reference.

```googlesql
CREATE TABLE books (
  title STRING NOT NULL PRIMARY KEY,
  author STRING
      OPTIONS (is_deprecated=true, comment="Replaced by authorId column"),
  authorId INT64 REFERENCES authors (id),
  category STRING OPTIONS (description="LCC Subclass")
)
```

--------------------------------

### Build and Run execute_query with Bazel

Source: https://github.com/google/googlesql/blob/master/README.md

Build the `execute_query` tool from source using Bazel and run it with the web interface enabled. This method is useful for development or when pre-built binaries are incompatible.

```bash
bazel run googlesql/tools/execute_query:execute_query -- --web
```

--------------------------------

### Truncate Datetime to ISO Year in GoogleSQL

Source: https://github.com/google/googlesql/blob/master/docs/datetime_functions.md

Use DATETIME_TRUNC with ISOYEAR to find the start of the ISO year and EXTRACT to get the ISO year number. Note that the ISO year may not align with the Gregorian calendar year.

```googlesql
SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

/*---------------------+
 | isoyear_boundary    |
 +---------------------+
 | 2014-12-29 00:00:00 |
 +---------------------+
 | isoyear_number    |
 +---------------------+
 | 2015              |
 +---------------------*/
```

--------------------------------

### Simple Protocol Buffer Creation

Source: https://github.com/google/googlesql/blob/master/docs/data-types.md

A basic example of creating a 'Chart' protocol buffer with literal values for rank and chart name.

```googlesql
SELECT
  key,
  name,
  NEW googlesql.examples.music.Chart { rank: 1 chart_name: '2' }
```

--------------------------------

### Get Current Datetime in GoogleSQL

Source: https://github.com/google/googlesql/blob/master/docs/datetime_functions.md

Returns the current date and time as a DATETIME object. The value is set at the start of the query statement and remains consistent for all invocations within that statement. An optional time zone parameter can be provided.

```googlesql
CURRENT_DATETIME([time_zone])
```

```googlesql
CURRENT_DATETIME
```

```googlesql
SELECT CURRENT_DATETIME() as now;

/*----------------------------+
 | now                        |
 +----------------------------+
 | 2016-05-19 10:38:47.046465 |
 +----------------------------*/
```