sql_window.R

suppressPackageStartupMessages(library(duckdb))
suppressWarnings(suppressPackageStartupMessages(library(tidyverse)))
con <- dbConnect(duckdb())
set.seed(1)
df <- tibble(
  id = sample(1:5, 10, replace = TRUE),
  x = sample(LETTERS[1:4], 10, replace = TRUE)
)
duckdb_register(con, "df", df, overwrite = TRUE)
q <- "select * from(
      select
           *,
           string_agg(x) over(
            partition by id order by id
            rows between unbounded preceding and unbounded following  
            exclude current row
   ) as grps
from (select distinct id, x from df order by x, id)
)
where grps is not null
order by id"
dbGetQuery(con, q)

performance-wise i feel like it would be better to just do filter(n() > 1) right aftergroup_by(id) instead of filtering by grps at the very end