### Downloading Test Database in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates how to download the `synpuf-10p.sqlite` test database file directly using a Julia command. It's a prerequisite for running the examples in the guide, providing a local SQLite database for FunSQL queries. The `DATABASE` variable will hold the path to the downloaded file.

```Julia
DATABASE = download("https://github.com/MechanicalRabbit/ohdsi-synpuf-demo/releases/download/20210412/synpuf-10p.sqlite")
```

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

### Downloading SQLite Database File (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet downloads a sample SQLite database file from a specified URL. It defines a constant `URL` for the database location and then uses the `download` function to fetch the file, storing its path in the `DATABASE` constant. This file is used for subsequent database connection examples.

```Julia
const URL = "https://github.com/MechanicalRabbit/ohdsi-synpuf-demo/releases/download/20210412/synpuf-10p.sqlite"
const DATABASE = download(URL)
```

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

### Creating Vocabulary Shortcuts in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Defines convenience functions `SNOMED` and `VISIT` as shortcuts for `ConceptByCode`, pre-setting the `vocabulary_id` to 'SNOMED' or 'Visit' respectively. This simplifies querying for common vocabularies.

```Julia
SNOMED(codes...) =
    ConceptByCode("SNOMED", codes...)

VISIT(codes...) =
    ConceptByCode("Visit", codes...)
```

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

### Applying MergeOverlappingIntervals to Visit Data in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia code demonstrates how to apply the `MergeOverlappingIntervals` transformation to a `visit_occurrence` table. It constructs a FunSQL query `q` to merge visits based on their start and end dates, then renders the generated SQL query to the console.

```Julia
q = From(:visit_occurrence) |>
    MergeOverlappingIntervals(Get.visit_start_date, Get.visit_end_date) |>
    Select(Get.person_id, Get.start_date, Get.end_date)

render(conn, q) |> print
```

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

### Referencing Columns with Get in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates various equivalent ways to create a column reference using the `Get` node in FunSQL.jl. It shows how to reference columns using dot notation (`Get.column_name`), symbol (`Get(:column_name)`), or string (`Get("column_name")`), all of which resolve to the same column reference.

```Julia
Get.year_of_birth
Get(:year_of_birth)
Get."year_of_birth"
Get("year_of_birth")
```

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

### Querying All Subtypes Recursively in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Demonstrates how to use the `WithSubtypes` pipeline to fetch a concept and all its recursive subtypes. The query is executed via `DBInterface.execute` and the comprehensive list of concepts is presented in a DataFrame.

```Julia
q = SNOMED("22298006") |>       # Myocardial infarction
    WithSubtypes()

DBInterface.execute(conn, q) |> DataFrame
```

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

### Installing SQLite.jl Package in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates how to add the SQLite.jl package to your Julia environment using the built-in Pkg manager. This package is a prerequisite for connecting to SQLite databases and using FunSQL with them. No specific inputs or outputs, just modifies the Julia environment.

```Julia
using Pkg

Pkg.add("SQLite")
```

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

### Defining ImmediateSubtypes Function in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Defines a FunSQL function `ImmediateSubtypes` to find direct subtypes of a given concept set. It performs a self-join on the `concept_relationship` table, filtering for 'Is a' relationships, and then joins with the `concept` table to get subtype details.

```Julia
ImmediateSubtypes() =
    As(:base) |>
    Join(From(:concept_relationship) |>
         Where(Get.relationship_id .== "Is a") |>
         As(:concept_relationship),
         on = Get.base.concept_id .== Get.concept_relationship.concept_id_2) |>
    Join(From(:concept),
         on = Get.concept_relationship.concept_id_1 .== Get.concept_id)
```

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

### Importing FunSQL Query Constructors (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet demonstrates how to import all available query constructors and the `render` function from the FunSQL.jl library. FunSQL does not export symbols by default, requiring explicit `using` statements to access its functionalities.

```Julia
using FunSQL:
        FunSQL, Agg, Append, As, Asc, Bind, CrossJoin, Define, Desc, Fun, From,
        Get, Group, Highlight, Iterate, Join, LeftJoin, Limit, Lit, Order,
        Partition, Select, Sort, Var, Where, With, WithExternal, render
```

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

### Using LeftJoin Alias in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example shows the use of the `LeftJoin` alias provided by FunSQL.jl, which simplifies the common `Join` operation with `left = true`. It performs the same left join as the previous example, correlating `person` and `location` tables on `location_id` and selecting `person_id` and `location.state`.

```Julia
using FunSQL: LeftJoin

From(:person) |>
LeftJoin(:location => From(:location),
             Get.location_id .== Get.location.location_id) |>
Select(Get.person_id, Get.location.state)
```

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

### Establishing SQLite Database Connection (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet establishes a connection to the SQLite database using `SQLite.jl` and `DBInterface.jl`. It creates a connection object `conn` that FunSQL can use to interact with the database, pointing to the previously downloaded `DATABASE` file.

```Julia
using SQLite

    const conn = DBInterface.connect(FunSQL.DB{SQLite.DB}, DATABASE)
```

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

### Establishing PostgreSQL Database Connection (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet demonstrates how to establish a connection to a PostgreSQL database using FunSQL and the previously bridged LibPQ.jl. It uses `DBInterface.connect` with `FunSQL.DB{LibPQ.Connection}` to create a connection object, assuming connection parameters are provided where `…` is indicated.

```Julia
const conn = DBInterface.connect(FunSQL.DB{LibPQ.Connection}, …)
```

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

### Downloading SQLite Database as Artifact (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet shows an alternative method to obtain the sample SQLite database by using Julia's artifact system. It leverages `Pkg.Artifacts` and `LazyArtifacts` to locate and access the database file, ensuring reproducible access to data dependencies.

```Julia
using Pkg.Artifacts, LazyArtifacts

    const DATABASE = joinpath(artifact"synpuf-10p", "synpuf-10p.sqlite")
```

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

### Querying Immediate Subtypes in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Illustrates how to apply the `ImmediateSubtypes` pipeline to a concept set (e.g., 'Myocardial infarction') to retrieve its direct subtypes. The query is executed using `DBInterface.execute` and the results are displayed in a DataFrame.

```Julia
q = SNOMED("22298006") |>       # Myocardial infarction
    ImmediateSubtypes()

DBInterface.execute(conn, q) |> DataFrame
```

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

### Bridging LibPQ.jl for DBInterface.jl Compatibility (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia code provides a manual bridge to make LibPQ.jl compatible with the DBInterface.jl package, which FunSQL relies on. It defines methods for `DBInterface.connect`, `DBInterface.prepare`, and `DBInterface.execute` that delegate to the corresponding LibPQ.jl functions, enabling seamless PostgreSQL interaction.

```Julia
using LibPQ
using DBInterface

DBInterface.connect(::Type{LibPQ.Connection}, args...; kws...) =
    LibPQ.Connection(args...; kws...)

DBInterface.prepare(conn::LibPQ.Connection, args...; kws...) =
    LibPQ.prepare(conn, args...; kws...)

DBInterface.execute(conn::Union{LibPQ.Connection, LibPQ.Statement}, args...; kws...) =
    LibPQ.execute(conn, args...; kws...)
```

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

### Establishing SQLite Database Connection with FunSQL

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This code establishes a connection to an SQLite database using `DBInterface.connect` and wraps it with `FunSQL.DB`. It requires the `FunSQL` and `SQLite` packages to be loaded. `DATABASE` is a placeholder for the database file path. The output is a `FunSQL` connection object (`conn`) that includes the database catalog.

```Julia
using FunSQL
using SQLite

conn = DBInterface.connect(FunSQL.DB{SQLite.DB}, DATABASE)
```

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

### Creating Basic FunSQL Query with From (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet creates a minimal FunSQL query object using the `From` constructor. It specifies that the query should select data from the table named `:person`, forming the basis for a `SELECT * FROM table` operation.

```Julia
q = From(:person)
```

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

### Executing Inpatient Visit Concept Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This code executes the `InpatientVisitConcept` query against the database connection `conn` and converts the result into a DataFrame. It allows for inspection of the defined inpatient visit concepts.

```Julia
DBInterface.execute(conn, InpatientVisitConcept()) |> DataFrame
```

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

### Direct Execution of FunSQL Query to DataFrame (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet shows a concise way to execute a FunSQL query object (`q`) directly and pipe its results into a `DataFrame`. This combines the rendering and execution steps into a single, streamlined operation for immediate data display.

```Julia
DBInterface.execute(conn, q) |> DataFrame
```

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

### Querying Males from Top Oldest Patients with ORDER, LIMIT, WHERE - FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This example illustrates FunSQL's flexibility by applying `ORDER` and `LIMIT` before `WHERE`. This order results in a subquery in the generated SQL, where the top 3 oldest patients are first selected, and then the male patients are filtered from that limited set. This demonstrates how operation order affects the query plan and results.

```Julia
q = From(:person) |>
    Order(Get.year_of_birth) |>
    Limit(3) |>
    Where(Get.gender_concept_id .== 8507)
```

```Julia
render(conn, q) |> print
```

```SQL
SELECT
  "person_2"."person_id",
  ⋮
  "person_2"."ethnicity_source_concept_id"
FROM (
  SELECT
    "person_1"."person_id",
    ⋮
    "person_1"."ethnicity_source_concept_id"
  FROM "person" AS "person_1"
  ORDER BY "person_1"."year_of_birth"
  LIMIT 3
) AS "person_2"
WHERE ("person_2"."gender_concept_id" = 8507)
```

```Julia
DBInterface.execute(conn, q) |> DataFrame
```

```DataFrame
2×18 DataFrame
 Row │ person_id  gender_concept_id  year_of_birth  month_of_birth  day_of_bir ⋯
     │ Int64      Int64              Int64          Int64           Int64      ⋯
─────┼──────────────────────────────────────────────────────────────────────────
   1 │    110862               8507           1911               4             ⋯
   2 │     42383               8507           1922               2
                                                              14 columns omitted
```

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

### Creating a Basic FunSQL Query from a Table

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates how to construct a basic FunSQL query using `From(:person)`. This creates a query object (`q`) that represents selecting all columns from the `person` table. It's the initial step before executing or rendering the query. Requires `FunSQL.From`.

```Julia
using FunSQL: From

q = From(:person)
```

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

### Selecting Current Timestamp with From(nothing) in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example provides an alternative way to achieve the effect of a query without a `From` node by explicitly using `From(nothing)`. This also results in a query that operates on a unit dataset, producing a single row of output, demonstrating the current timestamp.

```Julia
q = From(nothing) |>
        Select(Fun.current_timestamp())

sql = render(q)

print(sql)
```

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

### Initializing SQLString with Named Parameters in FunSQL.jl (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

This example demonstrates how to define an `SQLString` with named parameters. The `vars` keyword argument takes a vector of symbols representing the parameter names, ordered as they appear in the SQL query. This setup is crucial for later use with `DBInterface.execute` and the `pack` function to bind values to these parameters.

```Julia
sql = SQLString("SELECT * FROM person WHERE year_of_birth >= ?", vars = [:YEAR])
display(sql)
```

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

### Rendering Final Query as SQL in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet demonstrates how to render the final FunSQL query (`q`) into its equivalent SQL representation using `render(conn, q)`. The `print` function then outputs the generated SQL, which is useful for debugging or understanding the underlying database operations.

```Julia
render(conn, q) |> print
```

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

### Displaying Query Results with DataFrames (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet demonstrates how to convert the results of a database query (`res`) into a `DataFrame` object using the DataFrames.jl package. This provides a convenient and structured way to view and manipulate the tabular output.

```Julia
using DataFrames

    DataFrame(res)
```

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

### Rendering FunSQL Query to SQL with Connection

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This code demonstrates how to convert a FunSQL query (`q`) into its equivalent SQL string representation using `FunSQL.render`. It takes a database connection (`conn`) to infer the dialect and schema. The resulting SQL string (`sql`) can then be printed or used for debugging.

```Julia
using FunSQL: render

sql = render(conn, q)

print(sql)
```

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

### Equivalent FunSQL Query without @funsql Macro (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

Shows the traditional FunSQL syntax using `From`, `Where`, `Select`, and the pipe operator (`|>`) that is semantically equivalent to the `@funsql` macro example. This illustrates the underlying FunSQL structure that `@funsql` simplifies.

```Julia
using FunSQL: From, Get, Select, Where

q = From(:person) |> Where(Get.year_of_birth .> 1950) |> Select(Get.person_id)
```

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

### Table-Valued Function Usage Example (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

This Julia snippet demonstrates how to use table-valued functions, such as `Fun.regexp_matches`, within a `FROM` clause in FunSQL. It shows how to specify the output columns of the table-valued function for subsequent selection and manipulation.

```Julia
From(Fun.regexp_matches("2,3,5,7,11", "(\\d+)", "g"),
     columns = [:captures]) |>
Select(Fun."CAST(?[1] AS INTEGER)"(Get.captures))
```

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

### Creating a SQLTable with Positional Arguments (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

This example shows a more concise way to define a `SQLTable` by providing the table name and its columns as positional arguments to the constructor. This shorthand is useful for quickly defining tables where column names are simple strings or symbols.

```Julia
concept = SQLTable("concept", "concept_id", "concept_name", "vocabulary_id")
```

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

### Defining WithSubtypes Function in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Defines a FunSQL function `WithSubtypes` that recursively applies `ImmediateSubtypes` using `Iterate`. This allows retrieving a concept set along with all its transitive subtypes.

```Julia
WithSubtypes() =
    Iterate(ImmediateSubtypes())
```

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

### SQL Output for Table-Valued Function Example

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

This SQL snippet is the generated output for the FunSQL Julia example using a table-valued function. It illustrates how the `regexp_matches` function is translated into the `FROM` clause, with its output columns aliased for use in the `SELECT` statement.

```SQL
SELECT CAST("regexp_matches_1"."captures"[1] AS INTEGER) AS "_"
FROM regexp_matches('2,3,5,7,11', '(\d+)', 'g') AS "regexp_matches_1" ("captures")
```

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

### Representing SQL NULL with Missing in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example illustrates how the SQL `NULL` value is represented in FunSQL.jl using the Julia constant `missing`. It shows a query selecting `missing` and how `render` translates this into `SELECT NULL AS "_"` in SQL, demonstrating the mapping between Julia's `missing` and SQL's `NULL`.

```Julia
q = Select(missing)

render(conn, q) |> print
#-> SELECT NULL AS "_"
```

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

### Executing SQL Query with DBInterface (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet executes a raw SQL string (`sql`) against the database connection (`conn`) using the `DBInterface.execute` function. It returns the query results, which can then be further processed or displayed.

```Julia
res = DBInterface.execute(conn, sql)
```

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

### Generated SQL for Merging Overlapping Intervals

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This SQL query is generated by FunSQL.jl from the `MergeOverlappingIntervals` transformation. It uses window functions (`OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)`) to identify and group overlapping intervals, ultimately selecting the `person_id` and the minimum start date and maximum end date for each merged period.

```SQL
SELECT
  "visit_occurrence_3"."person_id",
  min("visit_occurrence_3"."visit_start_date") AS "start_date",
  max("visit_occurrence_3"."visit_end_date") AS "end_date"
FROM (
  SELECT
    "visit_occurrence_2"."person_id",
    (sum("visit_occurrence_2"."new") OVER (PARTITION BY "visit_occurrence_2"."person_id" ORDER BY "visit_occurrence_2"."visit_start_date", (- "visit_occurrence_2"."new") ROWS UNBOUNDED PRECEDING)) AS "period",
    "visit_occurrence_2"."visit_start_date",
    "visit_occurrence_2"."visit_end_date"
  FROM (
    SELECT
      "visit_occurrence_1"."person_id",
      "visit_occurrence_1"."visit_start_date",
      "visit_occurrence_1"."visit_end_date",
      (CASE WHEN ("visit_occurrence_1"."visit_start_date" <= (max("visit_occurrence_1"."visit_end_date") OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date" ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))) THEN 0 ELSE 1 END) AS "new"
    FROM "visit_occurrence" AS "visit_occurrence_1"
  ) AS "visit_occurrence_2"
) AS "visit_occurrence_3"
GROUP BY
  "visit_occurrence_3"."person_id",
  "visit_occurrence_3"."period"
```

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

### Specifying Join Inputs with Keyword Arguments in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet illustrates an alternative syntax for the `Join` constructor in FunSQL.jl, where both input pipelines (`tail` and `joinee`) and the join condition (`on`) are provided as keyword arguments. It performs a left join between `person` and `location` datasets, selecting `person_id` and `location.state`.

```Julia
Join(tail = From(:person),
         joinee = :location => From(:location),
         on = Get.location_id .== Get.location.location_id,
         left = true) |>
Select(Get.person_id, Get.location.state)
```

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

### Executing Inpatient Visit Occurrence Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This code executes the `InpatientVisitOccurrence` query against the database connection `conn` and converts the result into a DataFrame. It fetches the actual inpatient visit records from the database.

```Julia
DBInterface.execute(conn, InpatientVisitOccurrence()) |> DataFrame
```

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

### Using Grouping Sets with FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

Demonstrates how to use the `Group()` node with the new `sets` parameter to calculate totals and subtotals in SQL. Examples include `:cube`, `:rollup`, and custom grouping key sets, enabling advanced aggregation functionalities.

```Julia
From(:person) |> Group(:year_of_birth, sets = :cube)

From(:person) |> Group(:year_of_birth, :month_of_birth, sets = :rollup)

From(:person) |> Group(:year_of_birth, :gender_concept_id,
                         sets = [[:year_of_birth], [:gender_concept_id]])
```

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

### Executing Myocardial Infarction Concept Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This code executes the `MyocardialInfarctionConcept` query against the database connection `conn` and converts the result into a DataFrame. It demonstrates how to retrieve the defined concept and inspect its structure and content.

```Julia
DBInterface.execute(conn, MyocardialInfarctionConcept()) |> DataFrame
```

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

### Creating an Empty SQLCatalog (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

This code demonstrates initializing an empty `SQLCatalog` object without any tables. It shows the default state of a catalog, which includes a default `SQLDialect` and no tables, useful for starting a new catalog definition.

```Julia
SQLCatalog()
```

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

### Executing Myocardial Infarction During Inpatient Visit Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet executes the `MyocardialInfarctionDuringInpatientVisit` query against the database connection (`conn`) and converts the results into a DataFrame. This shows the conditions that occurred during an inpatient visit.

```Julia
DBInterface.execute(conn, MyocardialInfarctionDuringInpatientVisit()) |> DataFrame
```

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

### Recommended Nested References with @funsql Macro

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

Presents the recommended approach for handling references within FunSQL.jl queries using the `@funsql` macro. This example demonstrates how to define and join tables, and select columns using nested references, replacing the previously deprecated node-bound reference syntax.

```Julia
q = @funsql begin
    from(person)
    join(
        location => from(location),
        location_id == location.location_id)
    select(person_id, location.state)
end
```

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

### Querying DataFrame with From Node in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example illustrates using the `From` node with a Julia `DataFrame` as the data source. FunSQL serializes the `DataFrame` content directly into the generated SQL query as `VALUES` clauses. This is useful for correlating external data with database content, though for large DataFrames, loading them into the database first is recommended for performance.

```Julia
df = DataFrame(person_id = ["SQL", "Julia", "FunSQL"],
                   year_of_birth = [1974, 2012, 2021])

q = From(df)

render(conn, q) |> print
```

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

### Constructing Recursive Subtype Query with FunSQL.jl Iterate

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This Julia snippet demonstrates how to build a recursive query using FunSQL.jl's `Iterate` node. It starts from a `base` concept and repeatedly applies the `SubtypesOf` transformation, using `From(^)` to refer to the results of the previous iteration, effectively traversing the entire concept hierarchy until no new subtypes are found. The `render` function is then used to display the generated SQL query.

```Julia
using FunSQL: Iterate

q = base |>
    Iterate(SubtypesOf(From(^)))

render(conn, q) |> print
```

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

### Performing a Join with Aliased Left Branch in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet demonstrates a `Join` where the `person` table is aliased as `:person` using `As`. Similar to the previous example, columns from the aliased `person` branch are excluded from the output by default, resulting in only `visit_occurrence` columns being selected.

```Julia
q′ = From(:person) |> As(:person) |>
     Join(From(:visit_occurrence),
          on = Get.person.person_id .== Get.person_id)
```

```SQL
SELECT
  "visit_occurrence_1"."visit_occurrence_id",
  ⋮
  "visit_occurrence_1"."visit_source_concept_id"
FROM "person" AS "person_1"
JOIN "visit_occurrence" AS "visit_occurrence_1" ON ("person_1"."person_id" = "visit_occurrence_1"."person_id")
```

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

### SQL Output for Define() - New Version (SQL)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

This SQL snippet shows the optimized output generated by FunSQL.jl v0.11.0 for the `Define()` and `Where()` example. The `age` calculation is now performed once within a nested subquery, preventing expression duplication and improving query efficiency.

```SQL
SELECT
  "person_2"."person_id",
  "person_2"."age"
FROM (
  SELECT
    "person_1"."person_id",
    (2020 - "person_1"."year_of_birth") AS "age"
  FROM "person" AS "person_1"
) AS "person_2"
WHERE ("person_2"."age" >= 16)
```

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

### Rendering FunSQL Query to SQL Statement (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia snippet uses the `render` function from FunSQL to convert a FunSQL query object (`q`) into a raw SQL string. The `conn` object is provided to inform the renderer about the specific database dialect, ensuring correct SQL generation.

```Julia
sql = render(conn, q)
```

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

### Defining ConceptByCode Function in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Defines a FunSQL function `ConceptByCode` to retrieve concepts from the `concept` table. It supports querying by a single vocabulary and code, or by a vocabulary and multiple codes. It uses `From`, `Where`, `Fun.and`, and `Fun.in` for filtering.

```Julia
ConceptByCode(vocabulary, code) =
    From(:concept) |>
    Where(Fun.and(Get.vocabulary_id .== vocabulary,
                  Get.concept_code .== code))

ConceptByCode(vocabulary, codes...) =
    From(:concept) |>
    Where(Fun.and(Get.vocabulary_id .== vocabulary,
                  Fun.in(Get.concept_code, codes...)))
```

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

### SQL Output for Define() - Old Version (SQL)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

This SQL snippet represents the output generated by previous versions of FunSQL for the `Define()` and `Where()` example. It highlights that the expression for `age` was replicated in both the `SELECT` and `WHERE` clauses, potentially leading to redundant calculations.

```SQL
SELECT
  "person_1"."person_id",
  (2020 - "person_1"."year_of_birth") AS "age"
FROM "person" AS "person_1"
WHERE ((2020 - "person_1"."year_of_birth") >= 16)
```

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

### Representing SQL `BETWEEN` with FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates the various equivalent syntaxes for representing SQL functions and operators using the `Fun` node in FunSQL.jl. It shows how to use dot notation, symbol arguments, and string arguments to call SQL functions like `BETWEEN`.

```Julia
Fun.between(Get.year_of_birth, 1930, 1940)
Fun(:between, Get.year_of_birth, 1930, 1940)
Fun."between"(Get.year_of_birth, 1930, 1940)
Fun("between", Get.year_of_birth, 1930, 1940)
```

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

### Rendered SQL Query for SNOMED Concept Exclusion and Join

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This SQL snippet is the result of rendering the FunSQL query `q` from the previous example. It demonstrates the underlying SQL generated by FunSQL.jl, including recursive CTEs to handle SNOMED concept hierarchies (subtypes) and a `LEFT JOIN` with `WHERE IS NULL` to implement the concept exclusion logic, finally joining with `condition_occurrence`.

```SQL
WITH RECURSIVE "base_1" ("concept_id") AS (
  SELECT "concept_1"."concept_id"
  FROM "concept" AS "concept_1"
  WHERE
    ("concept_1"."vocabulary_id" = 'SNOMED') AND
    ("concept_1"."concept_code" = '22298006')
  UNION ALL
  SELECT "concept_2"."concept_id"
  FROM "base_1" AS "base_2"
  JOIN (
    SELECT
      "concept_relationship_1"."concept_id_1",
      "concept_relationship_1"."concept_id_2"
    FROM "concept_relationship" AS "concept_relationship_1"
    WHERE ("concept_relationship_1"."relationship_id" = 'Is a')
  ) AS "concept_relationship_2" ON ("base_2"."concept_id" = "concept_relationship_2"."concept_id_2")
  JOIN "concept" AS "concept_2" ON ("concept_relationship_2"."concept_id_1" = "concept_2"."concept_id")
),
"base_4" ("concept_id") AS (
  SELECT "concept_3"."concept_id"
  FROM "concept" AS "concept_3"
  WHERE
    ("concept_3"."vocabulary_id" = 'SNOMED') AND
    ("concept_3"."concept_code" = '70422006')
  UNION ALL
  SELECT "concept_4"."concept_id"
  FROM "base_4" AS "base_5"
  JOIN (
    SELECT
      "concept_relationship_3"."concept_id_1",
      "concept_relationship_3"."concept_id_2"
    FROM "concept_relationship" AS "concept_relationship_3"
    WHERE ("concept_relationship_3"."relationship_id" = 'Is a')
  ) AS "concept_relationship_4" ON ("base_5"."concept_id" = "concept_relationship_4"."concept_id_2")
  JOIN "concept" AS "concept_4" ON ("concept_relationship_4"."concept_id_1" = "concept_4"."concept_id")
)
SELECT
  "condition_occurrence_1"."person_id",
  "condition_occurrence_1"."condition_start_date"
FROM "condition_occurrence" AS "condition_occurrence_1"
JOIN (
  SELECT "base_3"."concept_id"
  FROM "base_1" AS "base_3"
  LEFT JOIN "base_4" AS "base_6" ON ("base_3"."concept_id" = "base_6"."concept_id")
  WHERE ("base_6"."concept_id" IS NULL)
) AS "base_7" ON ("condition_occurrence_1"."condition_concept_id" = "base_7"."concept_id")
ORDER BY "condition_occurrence_1"."condition_occurrence_id"
```

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

### Safely Accessing SQLTable Columns with `get` and String Key (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

Similar to the previous snippet, this example uses `get` to safely retrieve a column, but with a string key. It confirms that `get` works consistently with both symbol and string keys for `SQLTable` column access, returning the column or a default value.

```Julia
get(person, "person_id", nothing)
```

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

### Executing a Single Concept Query in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

Demonstrates how to use the `SNOMED` shortcut to query for a specific concept ('Myocardial infarction' with SNOMED code '22298006') and execute the FunSQL query using `DBInterface.execute` to retrieve results into a DataFrame.

```Julia
q = SNOMED("22298006")          # Myocardial infarction

DBInterface.execute(conn, q) |> DataFrame
```

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

### Safely Accessing Missing SQLTable Columns with `get` and Function (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

This example shows an alternative `get` overload where a function is provided to generate the default value if the key is not found. Here, an anonymous function returns `missing`, which is executed only if `:visit_occurrence` is not present in the `person` table.

```Julia
get(() -> missing, person, :visit_occurrence)
```

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

### Executing FunSQL Query and Displaying Results in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia code executes the previously defined FunSQL query `q` against a database connection `conn` using `DBInterface.execute`. The results are then converted into a `DataFrame` for tabular display, showing the merged `person_id`, `start_date`, and `end_date`.

```Julia
DBInterface.execute(conn, q) |> DataFrame
```

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

### Defining Inpatient Visit Concept in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet defines the 'Inpatient Visit' concept using the 'IP' visit type and includes its subtypes. It's a reusable component for identifying inpatient visit records in database queries.

```Julia
InpatientVisitConcept() =
    VISIT("IP") |>
    WithSubtypes()
```

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

### Defining Inpatient Visit Occurrence Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet defines a query to find occurrences of inpatient visits by joining the `visit_occurrence` table with the `InpatientVisitConcept`. It retrieves all records matching the inpatient visit definition.

```Julia
InpatientVisitOccurrence() =
    From(:visit_occurrence) |>
    Join(:concept => InpatientVisitConcept(),
         on = Get.visit_concept_id .== Get.concept.concept_id)
```

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

### Executing Myocardial Infarction Occurrence Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This code executes the `MyocardialInfarctionOccurrence` query against the database connection `conn` and converts the result into a DataFrame. It retrieves detailed records of myocardial infarction occurrences from the database.

```Julia
DBInterface.execute(conn, MyocardialInfarctionOccurrence()) |> DataFrame
```

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

### Constructing FunSQL Query Object

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This Julia code constructs a FunSQL query object `q` that represents the same SQL query as before. It uses FunSQL's `From`, `Where`, `Join`, and `Select` functions to build a composable query structure, avoiding direct string manipulation.

```julia
using FunSQL: As, From, Fun, Get, Join, Select, Where

q = From(:person) |>
    Where(Fun.between(Get.year_of_birth, 1930, 1940)) |>
    Join(From(:location) |> Where(Get.state .== "IL") |> As(:location),
         on = Get.location_id .== Get.location.location_id) |>
    Select(Get.person_id, :age => 2020 .- Get.year_of_birth)
```

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

### Reflecting SQLite Database Catalog with FunSQL

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates how to explicitly load the database catalog using `FunSQL.reflect`. This is useful for applications that open multiple connections to the same database and want to share a single catalog object. It connects to the SQLite database and then reflects its schema into a `catalog` object.

```Julia
using FunSQL: reflect

catalog = reflect(DBInterface.connect(SQLite.DB, DATABASE))
```

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

### Demonstrating Non-Existent SQL Function Handling in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates that FunSQL.jl does not validate the existence or correct usage of SQL functions during query construction. It shows how a query with a non-existent function (`frobnicate`) will be rendered into SQL but will fail upon execution against a database, resulting in a `SQLiteException`.

```Julia
q = From(:person) |>
    Select(Fun.frobnicate(Get.year_of_birth))

render(conn, q) |> print
#=>
SELECT frobnicate("person_1"."year_of_birth") AS "frobnicate"
FROM "person" AS "person_1"
=#

DBInterface.execute(conn, q)
#-> ERROR: SQLite.SQLiteException("no such function: frobnicate")
```

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

### Executing a Parameterized Inpatient Visit Query in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet demonstrates how to execute the `CorrelatedInpatientVisit` query. It instantiates the query with specific `person_id` (1780) and `date` ('2008-04-10'), then executes it against a database connection (`conn`) and converts the result into a DataFrame.

```Julia
q = CorrelatedInpatientVisit(1780, Date("2008-04-10"))

DBInterface.execute(conn, q) |> DataFrame
```

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

### Filtering Grouped Data with FunSQL.jl Where (HAVING)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example demonstrates using a `Where` node with an aggregate function (`Agg.max`) after a `Group` node. In FunSQL, this handles conditions on grouped data, which translates to a `HAVING` clause in SQL, filtering `person_id`s based on their latest visit date.

```Julia
q = From(:visit_occurrence) |>
    Group(Get.person_id) |>
    Where(Agg.max(Get.visit_end_date) .>= Fun.date("now", "-1 year"))

render(conn, q) |> print
```

```SQL
SELECT "visit_occurrence_1"."person_id"
FROM "visit_occurrence" AS "visit_occurrence_1"
GROUP BY "visit_occurrence_1"."person_id"
HAVING (max("visit_occurrence_1"."visit_end_date") >= date('now', '-1 year'))
```

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

### Creating a Synthetic Dataset for Temporal Filtering in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet creates a `DataFrame` named `events` to serve as a synthetic dataset for testing the `FilterByGap` pipeline. It contains sample `person_id` and `date` values, designed to illustrate how the gap filtering logic would apply to different event sequences.

```Julia
events = DataFrame([(person_id = 1, date = Date("2020-01-01")),    # ✓
                        (person_id = 1, date = Date("2020-02-01")),    # ✗
                        (person_id = 1, date = Date("2021-01-01")),    # ✓
                        (person_id = 1, date = Date("2021-05-01")),    # ✗
                        (person_id = 1, date = Date("2021-10-01")),    # ✗
                        (person_id = 2, date = Date("2020-01-01")),    # ✓
    ])
```

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

### Calculating Overall Average with FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example illustrates how to apply an aggregate function (`Agg.avg`) to the entire dataset by using `Group()` without any arguments. This is equivalent to omitting the `GROUP BY` clause in SQL, calculating a single aggregate value for the whole table.

```Julia
q = From(:person) |>
    Group() |>
    Select(Agg.avg(Get.year_of_birth))

render(conn, q) |> print
```

```SQL
SELECT avg("person_1"."year_of_birth") AS "avg"
FROM "person" AS "person_1"
```

```Julia
DBInterface.execute(conn, q) |> DataFrame
```

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

### Applying MergeIntervalsByGap to Visit Data in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia code applies the `MergeIntervalsByGap` transformation to the `visit_occurrence` table, using a `Day(365)` gap. It constructs a FunSQL query `q` to merge visits that are less than one year apart, then renders the generated SQL query.

```Julia
q = From(:visit_occurrence) |>
    MergeIntervalsByGap(Get.visit_start_date, Get.visit_end_date, Day(365)) |>
    Select(Get.person_id, Get.start_date, Get.end_date)

render(conn, q) |> print
```

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

### SQL ORDER BY and LIMIT Clause Example

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/two-kinds-of-sql-query-builders/index.md

This snippet demonstrates the basic usage of the `ORDER BY` and `LIMIT` clauses in SQL. It orders results by `year_of_birth` from the `person_1` alias and limits the output to 100 rows. This illustrates a common final stage in a SQL query.

```SQL
ORDER BY "person_1"."year_of_birth"
LIMIT 100
```

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

### Aggregating Across Joined Tables in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet shows how to perform an aggregate function (`Agg.max`) on a subquery that has been grouped and then joined back to the main query. The `As` node blocks direct access, requiring `Get` to traverse into the aliased subquery to access its aggregated results.

```Julia
q = From(:person) |>
    LeftJoin(:visit_group => From(:visit_occurrence) |> Group(Get.person_id),
             on = Get.person_id .== Get.visit_group.person_id) |>
    Select(Get.person_id,
           Get.visit_group |> Agg.max(Get.visit_start_date))

render(conn, q) |> print
```

```SQL
SELECT
  "person_1"."person_id",
  "visit_group_1"."max"
FROM "person" AS "person_1"
LEFT JOIN (
  SELECT
    max("visit_occurrence_1"."visit_start_date") AS "max",
    "visit_occurrence_1"."person_id"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  GROUP BY "visit_occurrence_1"."person_id"
) AS "visit_group_1" ON ("person_1"."person_id" = "visit_group_1"."person_id")
```

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

### Executing a FunSQL Query via DBInterface

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This code executes a FunSQL query (`q`) against an established database connection (`conn`) using `DBInterface.execute`. The result (`res`) is an iterable object containing the query's output. This is the standard way to retrieve data from the database using a FunSQL query.

```Julia
res = DBInterface.execute(conn, q)
```

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

### Executing FunSQL Query Object

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This Julia snippet executes the FunSQL query object `q` constructed previously. `DBInterface.execute` serializes the query object into a SQL statement and runs it against the database connection `conn`, returning the result as a `DataFrame`.

```julia
DBInterface.execute(conn, q) |> DataFrame
```

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

### Applying Temporal Gap Filter to Synthetic Data in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet demonstrates applying the `FilterByGap` pipeline to the `events` synthetic dataset. It constructs a query `q` that filters events with a 180-day gap, then executes this query and displays the results in a DataFrame, verifying the filter's correctness.

```Julia
q = From(events) |>
    FilterByGap(Get.date, Day(180))

DBInterface.execute(conn, q) |> DataFrame
```

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

### Ordering Query Results with FunSQL.jl (Descending)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example illustrates the use of the `Desc` modifier with the `Order` node in FunSQL.jl to sort query results in descending order. It applies a `DESC` keyword to the `ORDER BY` clause in the generated SQL, ensuring the output is sorted from largest to smallest value.

```Julia
using FunSQL: Desc

q = From(:person) |>
    Order(Get.year_of_birth |> Desc())

render(conn, q) |> print
```

```SQL
SELECT
  "person_1"."person_id",
  ⋮
  "person_1"."ethnicity_source_concept_id"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth" DESC
```

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

### Deprecated Node-Bound References in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/NEWS.md

Illustrates the deprecated syntax for node-bound references in FunSQL.jl, which will now fail. This example shows how `qₚ` and `qₗ` are used directly in the `Join` and `Select` operations, a pattern no longer supported after the v0.13.0 update.

```Julia
qₚ = From(:person)
qₗ = From(:location)
q = qₚ |>
      Join(qₗ, on = qₚ.location_id .== qₗ.location_id) |>
      Select(qₚ.person_id, qₗ.state)
```

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

### Preparing and Executing Raw SQL with SQLConnection in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

This example illustrates how `SQLConnection` acts as a proxy for `DBInterface` calls. It shows preparing and executing a raw SQL `SELECT` statement directly against the `SQLConnection` object, which then delegates the operation to the underlying raw database connection.

```Julia
DBInterface.prepare(conn, "SELECT * FROM person")
#-> SQLite.Stmt( … )

DBInterface.execute(conn, "SELECT * FROM person")
#-> SQLite.Query{false}( … )
```

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

### Querying Patients by Birth Year with Positional SQL Parameters

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example demonstrates executing a raw SQL query with positional parameters using `DBInterface.execute`. It filters the `person` table to find individuals whose `year_of_birth` falls within a specified range, providing the parameter values as a tuple.

```SQL
SELECT p.person_id
FROM person p
WHERE p.year_of_birth BETWEEN ? AND ?
```

```Julia
DBInterface.execute(conn, sql, (1930, 1940)) |> DataFrame
```

```Julia
3×1 DataFrame
 Row │ person_id
     │ Int64
─────┼───────────
   1 │      1780
   2 │     30091
   3 │     72120
```

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

### Performing a Join with Aliased Right Branch in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This snippet performs a `Join` between `person` and `visit_occurrence` tables. The `visit_occurrence` branch is aliased as `:visit` using `As`. By default, columns from the aliased branch are not included in the final output, as shown by the generated SQL.

```Julia
q = From(:person) |>
    Join(From(:visit_occurrence) |> As(:visit),
         on = Get.person_id .== Get.visit.person_id)
```

```SQL
SELECT
  "person_1"."person_id",
  ⋮
  "person_1"."ethnicity_source_concept_id"
FROM "person" AS "person_1"
JOIN "visit_occurrence" AS "visit_occurrence_1" ON ("person_1"."person_id" = "visit_occurrence_1"."person_id")
```

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

### Adding New Column with Define Node in FunSQL (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example illustrates the `Define` node, which is used to add new columns to a query while preserving all existing output columns from the preceding node. Here, a new column `:age` is defined based on a calculation, and it is appended to the existing patient data.

```Julia
using FunSQL: Define

q = From(:person) |>
        Define(:age => 2020 .- Get.year_of_birth)
```

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

### Limiting Query Results with FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates how to use the `Limit` node in FunSQL.jl to retrieve a specific range of rows from a dataset. It's crucial to apply `Limit` after `Order` to ensure deterministic results, as shown by the generated `LIMIT` and `OFFSET` clauses in the SQL.

```Julia
using FunSQL: Limit

q = From(:person) |>
    Order(Get.year_of_birth) |>
    Limit(1:3)

render(conn, q) |> print
```

```SQL
SELECT
  "person_1"."person_id",
  ⋮
  "person_1"."ethnicity_source_concept_id"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth"
LIMIT 3
OFFSET 0
```

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

### Fetching Test Database as an Artifact in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This Julia snippet shows an alternative, more robust method to obtain the test database by leveraging Julia's `Pkg.Artifacts` and `LazyArtifacts` packages. It fetches the `synpuf-10p.sqlite` file from a registered artifact, preventing repeated downloads and ensuring consistent access to the database for testing and development. The `DATABASE` variable will store the path to the artifact.

```Julia
using Pkg.Artifacts, LazyArtifacts

DATABASE = joinpath(artifact"synpuf-10p", "synpuf-10p.sqlite")
```

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

### Correlating Myocardial Infarction with Inpatient Visits in Julia

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/examples/index.md

This Julia function `MyocardialInfarctionDuringInpatientVisit` defines a FunSQL query that identifies myocardial infarction occurrences that happened during an inpatient visit. It uses `Fun.exists` with the previously defined `CorrelatedInpatientVisit` query to check for overlapping visits based on the person ID and condition start date.

```Julia
MyocardialInfarctionDuringInpatientVisit() =
    MyocardialInfarctionOccurrence() |>
    Where(Fun.exists(CorrelatedInpatientVisit(Get.person_id, Get.condition_start_date)))
```

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

### Disambiguating Join Columns using As Node in FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This example demonstrates how to use the `As` node (or the `=>` operator) in FunSQL.jl to disambiguate column names after a join. By aliasing the `location` dataset as `:location`, its columns are nested, preventing name clashes with `person` columns. It performs a left join and selects `person_id` and the aliased `location.state`.

```Julia
using FunSQL: As

From(:person) |>
LeftJoin(From(:location) |> As(:location),
             on = Get.location_id .== Get.location.location_id) |>
Select(Get.person_id, Get.location.state)
```

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

### Executing Parameterized FunSQL Query

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This Julia snippet demonstrates executing the `FindPatients` function without any parameters, effectively retrieving all patient data. The resulting FunSQL query object `q` is then executed via `DBInterface.execute` and the output is converted to a `DataFrame`.

```julia
q = FindPatients()

    DBInterface.execute(conn, q) |> DataFrame
```

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

### Creating a SQLCatalog with Multiple Tables and Settings (Julia)

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/test/other.md

This snippet constructs a `SQLCatalog` object, populating it with several `SQLTable` instances, including one with an alias (`:concept_vocabulary => vocabulary`). It also configures the target SQL dialect to `:sqlite` and sets a query cache size, demonstrating comprehensive catalog initialization.

```Julia
catalog = SQLCatalog(tables = [person, location, concept, :concept_vocabulary => vocabulary],
                         dialect = :sqlite,
                         cache = 128)
```

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

### Rendering FunSQL Query to SQL with SQLCatalog

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet illustrates rendering a FunSQL query (`q`) to SQL without an active database connection, by providing a `SQLCatalog` object. The `SQLCatalog` explicitly defines table schemas and the target SQL dialect (e.g., `:sqlite`), allowing SQL generation based on a predefined schema. This is useful for offline SQL generation or testing.

```Julia
using FunSQL: SQLCatalog, SQLTable

catalog = SQLCatalog(SQLTable(:person, columns = [:person_id, :year_of_birth]),
                         dialect = :sqlite)

sql = render(catalog, q)

print(sql)
```

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

### Performing a Left Join with FunSQL.jl

Source: https://github.com/mechanicalrabbit/funsql.jl/blob/master/docs/src/guide/index.md

This snippet demonstrates how to perform a left join operation in FunSQL.jl to combine `person` and `location` datasets. It correlates records based on `location_id` and includes all `person` records, even if no matching `location` is found. The output selects `person_id` and `location.state`.

```Julia
From(:person) |>
Join(:location => From(:location),
         Get.location_id .== Get.location.location_id,
         left = true) |>
Select(Get.person_id, Get.location.state)
```