> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Explore ClickHouse through UK property price data

# Analytical queries on UK property price data

In this tutorial you'll explore ClickHouse using the <Tooltip headline="UK Property Price dataset" tip="Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0." cta="Visit the source" href="https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads">UK Property Price dataset</Tooltip> which contains data of prices paid for real-estate property in England and Wales since 1995.

<h2 id="prerequisites">
  Prerequisites
</h2>

For this tutorial, you'll need:

* A [ClickHouse Cloud account](https://clickhouse.cloud/signUp?loc=docs-sample-datasets-uk-property-price) (\$300 in free credits when signing up)
* [A ClickHouse Cloud service](/get-started/setup/cloud#1-create-a-clickhouse-service)

<Steps titleSize="h2">
  <Step title="Create the table">
    1. Select **SQL console** from the the left hand menu
    2. Click the **+** tab next to the home icon to create a new query
    3. In the SQL editor type the following query, then click **Run**:

    ```sql theme={null}
    CREATE DATABASE uk;

    CREATE TABLE uk.uk_price_paid
    (
      price UInt32,
      date Date,
      postcode1 LowCardinality(String),
      postcode2 LowCardinality(String),
      type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
      is_new UInt8,
      duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
      addr1 String,
      addr2 String,
      street LowCardinality(String),
      locality LowCardinality(String),
      town LowCardinality(String),
      district LowCardinality(String),
      county LowCardinality(String)
    )
    ENGINE = MergeTree
    ORDER BY (postcode1, postcode2, addr1, addr2);
    ```

    <Tip>
      Take note of `ORDER BY (postcode1, postcode2, addr1, addr2)` which defines how ClickHouse sorts
      data on disk. Choosing an effective primary key in ClickHouse which matches your access patterns is crucial for query performance and storage efficiency.
      See ["Choosing a primary key"](/concepts/best-practices/choosing-a-primary-key) for more details.
    </Tip>

    See [https://www.gov.uk](https://www.gov.uk/guidance/about-the-price-paid-data) for a description of the fields.
  </Step>

  <Step title="Preprocess and insert the data" id="preprocess-import-data">
    You can use the `url` function to stream data into ClickHouse. Some preprocess is required first.
    The query below inserts 25+ million rows into the `uk_price_paid` table and performs the following preprocessing steps:

    * splits the `postcode` to two different columns - `postcode1` and `postcode2`, which is better for storage and queries
    * converts the `time` field to date as it only contains `00:00` time
    * ignores the [UUID](/reference/data-types/uuid) field because it isn't needed for analysis
    * transforms `type` and `duration` to more readable `Enum` fields using the [transform](/reference/functions/regular-functions/other-functions#transform) function
    * transforms the `is_new` field from a single-character string (`Y`/`N`) to a [UInt8](/reference/data-types/int-uint) field with 0 or 1
    * drops the last two columns since they all have the same value (which is 0)

    ```sql theme={null}
    INSERT INTO uk.uk_price_paid
    SELECT
      toUInt32(price_string) AS price,
      parseDateTimeBestEffortUS(time) AS date,
      splitByChar(' ', postcode)[1] AS postcode1,
      splitByChar(' ', postcode)[2] AS postcode2,
      transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
      b = 'Y' AS is_new,
      transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
      addr1,
      addr2,
      street,
      locality,
      town,
      district,
      county
    FROM url(
      'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
      'CSV',
      'uuid_string String,
      price_string String,
      time String,
      postcode String,
      a String,
      b String,
      c String,
      addr1 String,
      addr2 String,
      street String,
      locality String,
      town String,
      district String,
      county String,
      d String,
      e String'
    ) SETTINGS max_http_get_redirects=10;
    ```

    Wait for the data to insert - it will take a minute or two depending on the network speed.
  </Step>

  <Step title="Validate the data" id="validate-data">
    Let's verify it worked by seeing how many rows were inserted:

    ```sql theme={null}
    SELECT count()
    FROM uk.uk_price_paid
    ```

    At the time this query was run, the dataset had 27,450,499 rows. Let's see what the storage size is of the table in ClickHouse:

    ```sql theme={null}
    SELECT formatReadableSize(total_bytes)
    FROM system.tables
    WHERE name = 'uk_price_paid'
    ```

    Notice the size of the table is just 221.43 MiB, while the original dataset in uncompressed form is about 4 GiB.
    ClickHouse offers excellent data compression out of the box, but also allows you to [further tune compression per column](/reference/statements/create/table#column_compression_codec) if you need.
  </Step>

  <Step title="Run some queries" id="run-queries">
    With the data loaded, try out the following queries to get a sense of how fast analytical queries return results.
    The query below finds the average price per year across all of the data:

    ```sql theme={null}
    SELECT
      toYear(date) AS year,
      round(avg(price)) AS price,
      bar(price, 0, 1000000, 80
    )
    FROM uk.uk_price_paid
    GROUP BY year
    ORDER BY year
    ```

    The query below applies a filter to find the average price per year in London:

    ```sql theme={null}
    SELECT
      toYear(date) AS year,
      round(avg(price)) AS price,
      bar(price, 0, 2000000, 100
    )
    FROM uk.uk_price_paid
    WHERE town = 'LONDON'
    GROUP BY year
    ORDER BY year
    ```

    It looks like something happened to home prices in 2020! But that is probably not a surprise...

    The query below finds the most expensive neighborhoods:

    ```sql theme={null}
    SELECT
      town,
      district,
      count() AS c,
      round(avg(price)) AS price,
      bar(price, 0, 5000000, 100)
    FROM uk.uk_price_paid
    WHERE date >= '2020-01-01'
    GROUP BY
      town,
      district
    HAVING c >= 100
    ORDER BY price DESC
    LIMIT 100
    ```
  </Step>
</Steps>

<h2 id="next-steps">
  Next steps
</h2>

In this tutorial you created a table, preprocessed and loaded UK property price data into ClickHouse, and then
ran some analytical queries on that data.

Next you can:

* Learn how to speed up these queries with projections. See ["Projections"](/concepts/features/projections/projections) for examples which use the same dataset.
* Learn more about ClickHouse [core concepts](/concepts/core-concepts)
* Explore ClickHouse [best practices](/concepts/best-practices)
* Explore other [sample datasets](/get-started/sample-datasets)