---
source: prqlc/prqlc/tests/integration/queries.rs
expression: "# clickhouse:skip problems with DISTINCT ON\n# glaredb:skip — TODO: started raising an error on 2024-05-20, from https://github.com/PRQL/prql/actions/runs/9154902656/job/25198160283:\n    # ERROR: This feature is not implemented: Unsupported ast node in sqltorel:\n    # Substring { expr: Identifier(Ident { value: \"title\", quote_style: None }),\n    # substring_from: Some(Value(Number(\"2\", false))), substring_for:\n    # Some(Value(Number(\"5\", false))), special: true }\nfrom tracks\ngroup genre_id (\n  sort milliseconds\n  derive {\n    num = row_number this,\n    total = count this,\n    last_val = last track_id,\n  }\n  take 10\n)\nsort {genre_id, milliseconds}\nselect {track_id, genre_id, num, total, last_val}\nfilter genre_id >= 22\n"
input_file: prqlc/prqlc/tests/integration/queries/window.prql
snapshot_kind: text
---
WITH table_0 AS (
  SELECT
    track_id,
    genre_id,
    ROW_NUMBER() OVER (
      PARTITION BY genre_id
      ORDER BY
        milliseconds
    ) AS num,
    COUNT(*) OVER (
      PARTITION BY genre_id
      ORDER BY
        milliseconds ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS total,
    LAST_VALUE(track_id) OVER (
      PARTITION BY genre_id
      ORDER BY
        milliseconds
    ) AS last_val,
    milliseconds,
    ROW_NUMBER() OVER (
      PARTITION BY genre_id
      ORDER BY
        milliseconds
    ) AS _expr_0
  FROM
    tracks
),
table_1 AS (
  SELECT
    track_id,
    genre_id,
    num,
    total,
    last_val,
    milliseconds
  FROM
    table_0
  WHERE
    _expr_0 <= 10
    AND genre_id >= 22
)
SELECT
  track_id,
  genre_id,
  num,
  total,
  last_val
FROM
  table_1
ORDER BY
  genre_id,
  milliseconds