# MotherDuck Documentation - Example Datasets > A collections of open datasets and queries to get you started with DuckDB and MotherDuck Generated: 2026-08-25 > MotherDuck is a serverless cloud data warehouse built on DuckDB. It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics. ## Key capabilities - **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses) - **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session - **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language - **Data Sharing**: Share databases and query results with team members and external users - **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI - **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more - **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation ## When to use MotherDuck Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question. For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json. ## Account setup for agents If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request. `POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`. Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership. Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup. ## Included documentation Source: https://motherduck.com/docs/getting-started/sample-data-queries/hacker-news # Hacker News > Sample data from Hacker News stories to use for SQL querying of DuckDB and MotherDuck databases. ## Explore the data Interactive dashboard built on the Hacker News sample dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **Hacker News activity**. Dive ID: `813e3d2d-5e19-4925-b1e4-28d6777b620d`. ## About the dataset [Hacker News](https://news.ycombinator.com/) is a social news website focusing on computer science and entrepreneurship. It is run by Y Combinator, a startup accelerator, and it's known for its minimalist interface. Users can post stories (such as links to articles), comment on them, and vote them up or down, affecting their visibility. There are two ways to access the dataset: - Through the `sample_data` database, which contains a sample of the data (from **January 2022** to **November 2022**). This database is automatically attached to every MotherDuck account. - Through the `hacker_news` database, which contains the full dataset (from **2016** to **2025**). To attach the full `hacker_news` database, you can use the following command: :::note[`aws-us-east-1` region only] The `hacker_news` database is only available for accounts in the `aws-us-east-1` region. ::: ```sql ATTACH 'md:_share/hacker_news/de11a0e3-9d68-48d2-ac44-40e07a1d496b' AS hacker_news; ``` To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx) ## Example queries ### Most shared websites This query returns the top domains being shared on Hacker News. ```sql SELECT regexp_extract(url, 'http[s]?://([^/]+)/', 1) AS domain, count(*) AS count FROM sample_data.hn.hacker_news WHERE url IS NOT NULL AND regexp_extract(url, 'http[s]?://([^/]+)/', 1) != '' GROUP BY domain ORDER BY count DESC LIMIT 20; ``` ### Most commented stories each month This query calculates the total number of comments for each story and identifies the most commented story of each month. ```sql WITH ranked_stories AS ( SELECT title, 'https://news.ycombinator.com/item?id=' || id AS hn_url, descendants AS nb_comments, YEAR(timestamp) AS year, MONTH(timestamp) AS month, ROW_NUMBER() OVER ( PARTITION BY YEAR(timestamp), MONTH(timestamp) ORDER BY descendants DESC ) AS rn FROM sample_data.hn.hacker_news WHERE type = 'story' ) SELECT year, month, title, hn_url, nb_comments FROM ranked_stories WHERE rn = 1 ORDER BY year, month; ``` ### Most monthly voted stories This query determines the most voted story for each month. ```sql WITH ranked_stories AS ( SELECT title, 'https://news.ycombinator.com/item?id=' || id AS hn_url, score, YEAR(timestamp) AS year, MONTH(timestamp) AS month, ROW_NUMBER() OVER (PARTITION BY YEAR(timestamp), MONTH(timestamp) ORDER BY score DESC) AS rn FROM sample_data.hn.hacker_news WHERE type = 'story' ) SELECT year, month, title, hn_url, score FROM ranked_stories WHERE rn = 1 ORDER BY year, month; ``` ### Keyword analysis This query counts the monthly mentions a the keyword (here `duckdb`) in the title or text of Hacker News posts, organized by year and month. ```sql SELECT YEAR(timestamp) AS year, MONTH(timestamp) AS month, COUNT(*) AS keyword_mentions FROM sample_data.hn.hacker_news WHERE (title LIKE '%duckdb%' OR text LIKE '%duckdb%') GROUP BY year, month ORDER BY year ASC, month ASC; ``` ## Schema | column_name | column_type | null | key | default | extra | |-------------|-------------|------|-----|---------|-------| | title | VARCHAR | YES | | | | | url | VARCHAR | YES | | | | | text | VARCHAR | YES | | | | | dead | BOOLEAN | YES | | | | | by | VARCHAR | YES | | | | | score | BIGINT | YES | | | | | time | BIGINT | YES | | | | | timestamp | TIMESTAMP | YES | | | | | type | VARCHAR | YES | | | | | id | BIGINT | YES | | | | | parent | BIGINT | YES | | | | | descendants | BIGINT | YES | | | | | ranking | BIGINT | YES | | | | | deleted | BOOLEAN | YES | | | | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/air-quality # Air Quality > Sample data from the WHO Ambient Air Quality Database to use with DuckDB and MotherDuck ## Explore the data Interactive dashboard built on the WHO air quality dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **WHO Ambient Air Quality**. Dive ID: `dd4b9615-d668-4755-b564-880d2509f6b5`. ## About the dataset The [WHO Ambient Air Quality Database](https://www.who.int/publications/m/item/who-ambient-air-quality-database-(update-2023)) (6th edition, released in **May 2023**) compiles annual mean concentrations of nitrogen dioxide (NO2) and particulate matter (PM10, PM2.5) from ground measurements across over 8600 human settlements in more than 120 countries. This data, updated every 2-3 years since **2011**, primarily represents city or town averages and is used to monitor the Sustainable Development Goal Indicator 11.6.2, Air quality in cities. To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx) ## Example queries ### Annual city air quality rating This query assesses the average annual air quality in different cities per year based on WHO guidelines. It calculates the average concentrations of PM2.5, PM10, and NO2, then assigns an air quality rating of 'Good', 'Moderate', or 'Poor'. 'Good' indicates all pollutants are within WHO recommended levels, 'Poor' indicates all pollutants exceed WHO recommended levels, and 'Moderate' refers to any other scenario. The results are grouped and ordered by city and year. ```sql SELECT city, year, CASE WHEN AVG(pm25_concentration) <= 10 AND AVG(pm10_concentration) <= 20 AND AVG(no2_concentration) <= 40 THEN 'Good' WHEN AVG(pm25_concentration) > 10 AND AVG(pm10_concentration) > 20 AND AVG(no2_concentration) > 40 THEN 'Poor' ELSE 'Moderate' END AS airqualityrating FROM sample_data.who.ambient_air_quality GROUP BY city, year ORDER BY city, year; ``` ### Yearly average pollutant concentrations of a city This query calculates the yearly average concentrations of PM2.5, PM10, and NO2 in a given city, here `Berlin`. ```sql SELECT year, AVG(pm25_concentration) AS avg_pm25, AVG(pm10_concentration) AS avg_pm10, AVG(no2_concentration) AS avg_no2 FROM sample_data.who.ambient_air_quality WHERE city = 'Berlin' GROUP BY year ORDER BY year DESC; ``` ## Schema | column_name | column_type | null | key | default | extra | |--------------------|-------------|------|-----|---------|-------| | who_region | VARCHAR | YES | | | | | iso3 | VARCHAR | YES | | | | | country_name | VARCHAR | YES | | | | | city | VARCHAR | YES | | | | | year | BIGINT | YES | | | | | version | VARCHAR | YES | | | | | pm10_concentration | BIGINT | YES | | | | | pm25_concentration | BIGINT | YES | | | | | no2_concentration | BIGINT | YES | | | | | pm10_tempcov | BIGINT | YES | | | | | pm25_tempcov | BIGINT | YES | | | | | no2_tempcov | BIGINT | YES | | | | | type_of_stations | VARCHAR | YES | | | | | reference | VARCHAR | YES | | | | | web_link | VARCHAR | YES | | | | | population | VARCHAR | YES | | | | | population_source | VARCHAR | YES | | | | | latitude | FLOAT | YES | | | | | longitude | FLOAT | YES | | | | | who_ms | BIGINT | YES | | | | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/kaggle-movies # Kaggle Movies > A dataset of over 40,000 movies with titles, overviews, and pre-computed embeddings for semantic search. ## Explore the data Interactive dashboard with semantic search on the Kaggle Movies sample dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **Kaggle Movies**. Dive ID: `3428c1b0-3805-488c-85fd-a707ed818cf1`. ## About the dataset This dataset is a subset of the [Kaggle Movies Dataset](https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset), containing over 40,000 movie titles and overviews. It also includes pre-computed 512-dimensional vector embeddings (generated with OpenAI's `text-embedding-3-small` model) for both the title and overview fields, making it useful for experimenting with [semantic search](/key-tasks/ai-and-motherduck/text-search-in-motherduck/) in MotherDuck. ## How to query the dataset This dataset is available as part of the `sample_data` database, which is automatically attached to every MotherDuck account. ## Example queries ### Browse movies ```sql SELECT title, overview FROM sample_data.kaggle.movies LIMIT 10; ``` ### Find similar movies using vector search Use the pre-computed embeddings together with the [`embedding`](/sql-reference/motherduck-sql-reference/ai-functions/embedding/) function to find movies similar to a search query: ```sql SELECT title, overview, array_cosine_similarity( overview_embeddings, embedding('a space adventure with aliens') ) AS similarity FROM sample_data.kaggle.movies WHERE overview IS NOT NULL ORDER BY similarity DESC LIMIT 10; ``` ### Find movies similar to another movie ```sql WITH target AS ( SELECT overview_embeddings FROM sample_data.kaggle.movies WHERE title = 'The Matrix' LIMIT 1 ) SELECT m.title, m.overview, array_cosine_similarity(m.overview_embeddings, t.overview_embeddings) AS similarity FROM sample_data.kaggle.movies m, target t WHERE m.title != 'The Matrix' ORDER BY similarity DESC LIMIT 10; ``` ## Schema | Column Name | Column Type | Description | |-----------------------|-------------|-----------------------------------------------------------------| | title | VARCHAR | Movie title | | overview | VARCHAR | Short description or synopsis of the movie | | title_embeddings | FLOAT[512] | Pre-computed vector embedding of the title | | overview_embeddings | FLOAT[512] | Pre-computed vector embedding of the overview | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/foursquare # Foursquare > Foursquare Open Source Places (FSQ OS Places) is a global, open-source dataset of over 100 million points of interest (POI) ## Explore the data Interactive dashboard built on the Foursquare Open Source Places dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **Foursquare Open Source Places**. Dive ID: `d080c8fa-76c4-4720-9d5b-bdc6a6edda6f`. ## About the dataset [Foursquare](https://docs.foursquare.com/data-products/docs/fsq-places-open-source) Open Source Places (FSQ OS Places) is a global, open-source dataset of over 100 million points of interest (POI), featuring 22 core attributes, updated monthly, and designed to support geospatial applications with a collaborative, AI- and human-powered data curation system. This database is updated monthly, we host however a snapshot of 2025-01-10. You have two tables : - `fsq_os_places` (Places) : a global dataset of over 100 million points of interest (POIs) with detailed location, business, and contact information. - `fsq_os_categories` (Categories) : a hierarchical classification of POIs with up to six levels, detailing category names and IDs. :::note[`aws-us-east-1` region only] This database is only available for accounts in the `aws-us-east-1` region. ::: You can attach the `foursquare` database to your account by running the following command: ```sql ATTACH 'md:_share/foursquare/0cbf467d-03b0-449e-863a-ce17975d2c0b' AS foursquare; ``` ## Example queries The following queries assume that the current database connected is `foursquare`. Run `use foursquare` to switch to it. ### Countries with the most places ```sql SELECT country, COUNT(*) AS places FROM fsq_os_places GROUP BY country ORDER BY places DESC LIMIT 10; ``` ## Schema ### fsq_os_places - places dataset | Column Name | Type | Description | |--------------------|------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | fsq_place_id | String | The unique identifier of a Foursquare POI. Use this ID to view a venue at: `foursquare.com/v/{fsq_place_id}ud` | | name | String | Business name of a POI | | latitude/longitude | Decimal | Decimal coordinates (WGS84 datum) up to 6 decimal places. Derived from third-party sources, user input, and corrections. Default geocode type: front door or rooftop. | | address | String | User-entered street address of the venue | | locality | String | City, town, or equivalent where the POI is located | | region | String | State, province, or territory. Abbreviations used in US, CA, AU, BR; full names elsewhere | | postcode | String | Postal code or equivalent, formatted based on country (e.g., 5-digit US ZIP code) | | admin_region | String | Additional sub-division (e.g., Scotland) | | post_town | String | Town/place used in postal addressing (may differ from geographic location) | | po_box | String | Post Office Box | | country | String | 2-letter ISO Country Code | | date_created | Date | Date the POI entered the database (not necessarily the opening date) | | date_refreshed | Date | Last date any reference was refreshed through crawl, users, or validation | | date_closed | Date | Date the POI was marked closed in the database (not necessarily actual closure date) | | tel | String | Telephone number with local formatting | | website | String | URL to the POI’s (or chain’s) website | | email | String | Primary contact email address, if available | | facebook_id | String | POI's Facebook ID, if available | | instagram | String | POI's Instagram handle, if available | | twitter | String | POI's Twitter handle, if available | | fsq_category_ids | Array (String) | ID(s) of the most granular category(ies). See the Categories page for details | | fsq_category_labels| Array (String) | Label(s) of the most granular category(ies). See the Categories page for details | | placemaker_url | String | Link to the POI’s review page in PlaceMaker Tools for suggesting edits or reviewing pending changes | | geom | wkb | Geometry of the POI in WKB format for visualization through the vector tiling service | | bbox | struct | An area defined by two longitudes and two latitudes: latitude is a decimal number between -90.0 and 90.0; longitude is a decimal number between -180.0 and 180.0. `bbox:struct xmin:double ymin:double xmax:double ymax:double` | --- ### fsq_os_categories - category dataset | Column Name | Type | Description | |----------------------|---------|-----------------------------------------------------------------------------------------------------| | category_id | String | Unique identifier of the Foursquare category (BSON format) | | category_level | Integer | Hierarchy depth of the category (1-6) | | category_name | String | Name of the most granular category | | category_label | String | Full category hierarchy separated by `>` | | level1_category_id | String | Unique ID of the first-level category | | level1_category_name | String | Name of the first-level category | | level2_category_id | String | Unique ID of the second-level category | | level2_category_name | String | Name of the second-level category | | level3_category_id | String | Unique ID of the third-level category | | level3_category_name | String | Name of the third-level category | | level4_category_id | String | Unique ID of the fourth-level category | | level4_category_name | String | Name of the fourth-level category | | level5_category_id | String | Unique ID of the fifth-level category | | level5_category_name | String | Name of the fifth-level category | | level6_category_id | String | Unique ID of the sixth-level category | | level6_category_name | String | Name of the sixth-level category | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/nyc-311-data # NYC 311 Complaint Data > New York City provides data from 311 call service requests. This data can be used as sample data for DuckDB and MotherDuck SQL queries. ## Explore the data Interactive dashboards built on the NYC sample datasets. Use them as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **NYC 311 service requests**. Dive ID: `1b14654c-0ad0-4ada-9b89-1394302e3b30`. Embedded Dive: **NYC taxi operations**. Dive ID: `1ac766f5-d5cb-4d31-a87d-e0920a500fd3`. ## About the dataset The [New York City 311 Service Requests Data](https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9) provides information on requests to the city's complaint service from 2010 to the present. NYC311 responds to thousands of inquiries, comments and requests from customers every single day. This dataset represents only service requests that can be directed to specific agencies. This dataset is updated daily and expected values for many fields will change over time. The lists of expected values associated with each column are not exhaustive. Each row of data contains information about the service request, including complaint type, responding agency, and geographic location. However the data does not reveal any personally identifying information about the customer who made the request. This dataset describes site-specific non-emergency complaints (also known as “service requests”) made by customers across New York City about a variety of topics, including noise, sanitation, and street quality. To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx) ## Example queries ### The most common complaints in 2018 ```sql SELECT UPPER(complaint_type), COUNT(1) FROM sample_data.nyc.service_requests WHERE DATE_PART('year', created_date) = 2018 GROUP BY 1 HAVING COUNT(*) > 1000 ORDER BY 2 DESC; ``` ## Schema The columns have been renamed to `lower_case_underscore` format for ease of typing. For more details on column data than below, see the associated data dictionary at that link above, in an Excel file. | column_name | column_type | null | description | |--------------------------------|---------------|--------|-------------| | unique_key | BIGINT | YES | Unique identifier of a Service Request (SR) in the open data set. Each 311 service request is assigned a number that distinguishes it as a separate case incident. | | created_date | TIMESTAMP | YES | The date and time that a Customer submits a Service Request. | | closed_date | TIMESTAMP | YES | The date and time that an Agency closes a Service Request. | | agency | VARCHAR | YES | Acronym of responding City Government Agency or entity responding to 311 Service Request. | | agency_name | VARCHAR | YES | Full agency name of responding City Government Agency, or entity responding to 311 service request. | | complaint_type | VARCHAR | YES | This is the first level of a hierarchy identifying the topic of the incident or condition. Complaint Type broadly describes the topic of the incident or condition and are defined by the responding agencies. | | descriptor | VARCHAR | YES | This is associated to the Complaint Type, and provides further detail on the incident or condition. Descriptor values are dependent on the Complaint Type, and are not always required in the service request. | | location_type | VARCHAR | YES | Describes the type of location used in the address information | | incident_zip | VARCHAR | YES | Zip code of the incident address | | incident_address | VARCHAR | YES | House number and street name of incident address | | street_name | VARCHAR | YES | Street name of incident address | | cross_street_1 | VARCHAR | YES | First Cross street based on the geo validated incident location.| | cross_street_2 | VARCHAR | YES | Second Cross Street based on the geo validated incident location | | intersection_street_1 | VARCHAR | YES | First intersecting street based on geo validated incident location | | intersection_street_2 | VARCHAR | YES | Second intersecting street based on geo validated incident location | | address_type | VARCHAR | YES | Type of information available about the incident location: Address; Block face; Intersection; LatLong; Placename | | city | VARCHAR | YES | In this dataset, City can refer to a borough or neighborhood. MANHATTAN, BROOKLYN, BRONX, STATEN ISLAND, or in QUEENS, specific neighborhood name | | landmark | VARCHAR | YES | If the incident location is identified as a Landmark the name of the landmark will display here. Can refer to any noteworthy location, including but not limited to, parks, hospitals, airports, sports facilities, performance spaces, etc. | | facility_type | VARCHAR | YES | If applicable, this field describes the type of city facility associated to the service request: DSNY Garage, Precinct, School, School District, N/A | | status | VARCHAR | YES | Current status of the service request submitted: Assigned, Canceled, Closed, Pending | | due_date | TIMESTAMP | YES | Date when responding agency is expected to update the SR. This is based on the Complaint Type and internal Service Level Agreements (SLAs) | | resolution_description | VARCHAR | YES | Describes the last action taken on the service request by the responding agency. May describe next or future steps. | | resolution_action_updated_date | TIMESTAMP | YES | Date when responding agency last updated the service request. | | bbl | VARCHAR | YES | Parcel number that identifies the location of the building or property associated with the service request. The block is a subset of a borough. The lot is a subset of a block unique within a borough and block. | | borough | VARCHAR | YES | The borough number is: 1. Manhattan (New York County) 2. Bronx (Bronx County) 3. Brooklyn (Kings County) 4. Queens (Queens County) 5. Staten Island (Richmond County) | | x_coordinate_state_plane | VARCHAR | YES | Geo validated, X coordinate of the incident location. X coordinate of the incident location. For more information about NY State Plane Coordinate Zones: https://data.gis.ny.gov/datasets/ny-state-plane-coordinate-system-zones/explore | | y_coordinate_state_plane | VARCHAR | YES | Geo validated, Y coordinate of the incident location. Y coordinate of the incident location. For more information about NY State Plane Coordinate Zones: https://data.gis.ny.gov/datasets/ny-state-plane-coordinate-system-zones/explore | | open_data_channel_type | VARCHAR | YES | Indicates how the service request was submitted to 311: Phone, Online, Other (submitted by other agency) | | park_facility_name | VARCHAR | YES | If the incident location is a Parks Dept facility and service requests pertains to a facility managed by NYC Parks (DPR), the name of the facility will appear here | | park_borough | VARCHAR | YES | The borough of incident if the service request is pertaining to a NYC Parks Dept facility (DPR) | | vehicle_type | VARCHAR | YES | Data provided if service request pertains to a vehicle managed by the Taxi and Limousine Commission (TLC): Ambulette / Paratransit; Car Service; Commuter Van; Green Taxi | | taxi_company_borough | VARCHAR | YES | Data provided if service request pertains to a vehicle managed by the Taxi and Limousine Commission (TLC). | | taxi_pick_up_location | VARCHAR | YES | If the incident pertains a vehicle managed by the Taxi and Limousine Commission (TLC), this field displays the taxi pick up location | | bridge_highway_name | VARCHAR | YES | If the incident is identified as a Bridge/Highway, the name will be displayed here | | bridge_highway_direction | VARCHAR | YES | If the incident is identified as a Bridge/Highway, the direction where the issue took place would be displayed here. | | road_ramp | VARCHAR | YES | If the incident location was Bridge/Highway this column differentiates if the issue was on the Road or the Ramp. | | bridge_highway_segment | VARCHAR | YES | Additional information on the section of the Bridge/Highway were the incident took place. | | latitude | DOUBLE | YES | Geo based Latitude of the incident location in decimal degrees | | longitude | DOUBLE | YES | Geo based Longitude of the incident location in decimal degrees | | community_board | VARCHAR | YES | Community boards are local representative bodies. There are 59 community boards throughout the City. For more information on Community Boards: [NYC government website](https://www.nyc.gov/site/cau/community-boards/community-boards.page) | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/pypi # PyPi Data > Want to know how users find and install software you've developed for the Python Community? This DuckDB and MotherDuck database allows you to use SQL to perform data analysis on PyPi data. ## Explore the data Interactive dashboard built on the DuckDB PyPI download stats. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **DuckDB PyPI downloads**. Dive ID: `c75e16cc-64ed-4960-a2ba-470f47ccf605`. ## About the dataset PyPi is the Python Package Index, a repository of software packages for the Python programming language. It is a central repository that allows users to find and install software developed and shared by the Python community. The dataset includes information about packages, releases, and downloads on the `duckdb` python package. It's refreshed **weekly** and you can visit the [DuckDB Stats dashboard](https://duckdbstats.com). ## How to query the dataset A dedicated shared database is maintained to query the dataset. :::note[`aws-us-east-1` region only] This database is only available for accounts in the `aws-us-east-1` region. ::: To attach it to your workspace, you can use the following command: ```sql ATTACH 'md:_share/duckdb_stats/1eb684bf-faff-4860-8e7d-92af4ff9a410' AS duckdb_stats; ``` ## Example queries The following queries assume that the current database connected is `duckdb_stats`. Run `use duckdb_stats` to switch to it. ### Get weekly download stats ```sql SELECT DATE_TRUNC('week', download_date) AS week_start_date, version, country_code, python_version, SUM(daily_download_sum) AS weekly_download_sum FROM duckdb_stats.main.pypi_daily_stats GROUP BY ALL ORDER BY week_start_date ``` ## Schema ### pypi_file_downloads This table contains the raw data. Each row represents a download from PyPi. | column_name | column_type | null | |--------------|----------------------------------------------------------------------------------------------------------------|------| | timestamp | TIMESTAMP | YES | | country_code | VARCHAR | YES | | url | VARCHAR | YES | | project | VARCHAR | YES | | file | STRUCT(filename VARCHAR, project VARCHAR, "version" VARCHAR, "type" VARCHAR) | YES | | details | STRUCT("installer" STRUCT("name" VARCHAR, "version" VARCHAR), "python" VARCHAR, "implementation" STRUCT("name" VARCHAR, "version" VARCHAR), "distro" STRUCT("name" VARCHAR, "version" VARCHAR, "id" VARCHAR, "libc" STRUCT("lib" VARCHAR, "version" VARCHAR)), "system" STRUCT("name" VARCHAR, "release" VARCHAR), "cpu" VARCHAR, "openssl_version" VARCHAR, "setuptools_version" VARCHAR, "rustc_version" VARCHAR, "ci" BOOLEAN) | YES | | tls_protocol | VARCHAR | YES | | tls_cipher | VARCHAR | YES | ### pypi_daily_stats This table is a daily aggregation of the raw data. It contains the following columns: | column_name | column_type | null | |-------------------|-------------|------| | load_id | VARCHAR | YES | | download_date | DATE | YES | | system_name | VARCHAR | YES | | system_release | VARCHAR | YES | | version | VARCHAR | YES | | project | VARCHAR | YES | | country_code | VARCHAR | YES | | cpu | VARCHAR | YES | | python_version | VARCHAR | YES | | daily_download_sum| BIGINT | YES | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/stackoverflow # StackOverflow Data > Sample data from StackOverflow to use with DuckDB and MotherDuck to understand SQL-based data analytics. ## Explore the data Interactive dashboard built on the full Stack Overflow archive. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **Stack Overflow Archive**. Dive ID: `eb4c2b4e-5b0c-4c13-833c-6d97989ea746`. ## About the dataset [Stack Overflow](https://stackoverflow.com/) is a website dedicated to providing professional and enthusiast programmers a platform to learn and share knowledge. It features questions and answers on a wide range of topics in computer programming and is renowned for its community-driven approach. Users can ask questions, provide answers, vote on questions and answers, and earn reputation points and badges for their contributions. The dataset includes a complete **data dump up to May 2023**, covering posts, comments, users, badges, and related metrics. You can read more about the dataset in our blog series [part 1](https://motherduck.com/blog/exploring-stackoverflow-with-duckdb-on-motherduck-1/) and [part 2](https://motherduck.com/blog/exploring-stackoverflow-with-duckdb-on-motherduck-2/). ## How to query the dataset As this dataset is quite large, it's not part of the `sample_data` database. Instead, you can find it as a dedicated shared database. :::note[`aws-us-east-1` region only] This database is only available for accounts in the `aws-us-east-1` region. ::: To attach it to your workspace, you can use the following command: ```sql ATTACH 'md:_share/stackoverflow/6c318917-6888-425a-bea1-5860c29947e5' AS stackoverflow; ``` ## Example queries The following queries assume that the current database connected is `stackoverflow`. Run `use stackoverflow` to switch to it. ### List the top 5 posts that received the most votes ```sql SELECT posts.Title, COUNT(votes.Id) AS VoteCount FROM posts JOIN votes ON posts.Id = votes.PostId GROUP BY posts.Title ORDER BY VoteCount DESC LIMIT 5; ``` ### Find the top 5 posts with the highest view count: ```sql SELECT Title, ViewCount FROM posts ORDER BY ViewCount DESC LIMIT 5; ``` ## Schema ### Badges | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | UserId | BIGINT | YES | | | | | Name | VARCHAR | YES | | | | | Date | TIMESTAMP | YES | | | | | Class | BIGINT | YES | | | | | TagBased | BOOLEAN | YES | | | | ### Comments | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | PostId | BIGINT | YES | | | | | Score | BIGINT | YES | | | | | Text | VARCHAR | YES | | | | | CreationDate | TIMESTAMP | YES | | | | | UserId | BIGINT | YES | | | | | ContentLicense | VARCHAR | YES | | | | ### Post links | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | CreationDate | TIMESTAMP | YES | | | | | PostId | BIGINT | YES | | | | | RelatedPostId | BIGINT | YES | | | | | LinkTypeId | BIGINT | YES | | | | ### Posts | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | PostTypeId | BIGINT | YES | | | | | AcceptedAnswerId | BIGINT | YES | | | | | CreationDate | TIMESTAMP | YES | | | | | Score | BIGINT | YES | | | | | ViewCount | BIGINT | YES | | | | | Body | VARCHAR | YES | | | | | OwnerUserId | BIGINT | YES | | | | | LastEditorUserId | BIGINT | YES | | | | | LastEditorDisplayName | VARCHAR | YES | | | | | LastEditDate | TIMESTAMP | YES | | | | | LastActivityDate | TIMESTAMP | YES | | | | | Title | VARCHAR | YES | | | | | Tags | VARCHAR | YES | | | | | AnswerCount | BIGINT | YES | | | | | CommentCount | BIGINT | YES | | | | | FavoriteCount | BIGINT | YES | | | | | CommunityOwnedDate | TIMESTAMP | YES | | | | | ContentLicense | VARCHAR | YES | | | | ### Tags | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | TagName | VARCHAR | YES | | | | | Count | BIGINT | YES | | | | | ExcerptPostId | BIGINT | YES | | | | | WikiPostId | BIGINT | YES | | | | ### Votes | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | PostId | BIGINT | YES | | | | | VoteTypeId | BIGINT | YES | | | | | CreationDate | TIMESTAMP | YES | | | | ### Users | column_name | column_type | null | key | default | extra | |---|---|---|---|---|---| | Id | BIGINT | YES | | | | | Reputation | BIGINT | YES | | | | | CreationDate | TIMESTAMP | YES | | | | | DisplayName | VARCHAR | YES | | | | | LastAccessDate | TIMESTAMP | YES | | | | | AboutMe | VARCHAR | YES | | | | | Views | BIGINT | YES | | | | | UpVotes | BIGINT | YES | | | | | DownVotes | BIGINT | YES | | | | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/stackoverflow-survey # StackOverflow Survey Data > Data from the StackOverflow Developer Survey from 2017 to 2024. ## Explore the data Interactive dashboard built on the survey data. Use it as a starting point for your own [Dives](/key-tasks/dives/). Embedded Dive: **Stack Overflow Developer Survey**. Dive ID: `9ee6c071-d467-4018-a819-a5f2e1a0586d`. ## About the dataset Each year, [Stack Overflow conducts a survey](https://survey.stackoverflow.co/) of developers to understand the trends in the developer community. The survey covers a wide range of topics, including programming languages, frameworks, databases, and platforms, as well as developer demographics, education, and career satisfaction. Starting from 2017, StackOverflow provided consistent schema and data format for the survey data, making it a great dataset to analyze trends in the developer community over the years. The source is data are a series of CSV files that has been merged into a single schema with two tables for easy querying. ## How to query the dataset This dataset is available as part of the `sample_data` database, which is automatically attached to every MotherDuck account. ## Example queries ### List the most popular programming languages in 2024 ```sql SELECT language, COUNT(*) AS count FROM ( SELECT UNNEST(STRING_SPLIT(LanguageHaveWorkedWith, ';')) AS language FROM sample_data.stackoverflow_survey.survey_results where year='2024' ) AS languages GROUP BY language ORDER BY count DESC; ``` ### Top 10 countries with the most respondents in 2024 ```sql SELECT Country, COUNT(*) AS Respondents FROM sample_data.stackoverflow_survey.survey_results WHERE year = '2024' GROUP BY Country ORDER BY Respondents DESC LIMIT 10; ``` ### Correlation between remote work and job satisfaction in 2024 ```sql SELECT RemoteWork, AVG(CAST(JobSat AS DOUBLE)) AS AvgJobSatisfaction, COUNT(*) AS RespondentCount FROM sample_data.stackoverflow_survey.survey_results WHERE JobSat NOT IN ('NA', 'Slightly satisfied', 'Neither satisfied nor dissatisfied', 'Very dissatisfied', 'Very satisfied', 'Slightly dissatisfied') AND RemoteWork NOT IN ('NA') AND YEAR='2024' GROUP BY ALL ``` ## Schema ### stackoverflow_survey.survey_results This table contains all the survey results from 2017 to 2024. Each column represents a question from the survey. As questions change from year to year, the columns may vary a bit and the table is quite large. ### stackoverflow_survey.survey_schema This table contains the schema of the survey results. `qname` is the name of the question, which is also the column name in the `survey_results` table. `question` is the full question text. | Column Name | Column Type | |---------------|-------------| | qname | VARCHAR | | question | VARCHAR | | qid | VARCHAR | | force_resp | VARCHAR | | type | VARCHAR | | selector | VARCHAR | | year | VARCHAR | --- Source: https://motherduck.com/docs/getting-started/sample-data-queries/datasets # Example Datasets > A collections of open datasets and queries to get you started with DuckDB and MotherDuck We have prepared a series of datasets for you to [dive](/key-tasks/dives/) into MotherDuck! ## sample_data The `sample_data` database is automatically attached to every MotherDuck account regardless of your region. You can start querying the following tables right away: | `schema.table` | Description | |--------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------| | [`who.ambient_air_quality`](air-quality.md) | Historical air quality data from the World Health Organization. | | [`nyc.taxi`](nyc-311-data.md) | Taxi ride data from November 2020 | | [`nyc.rideshare`](nyc-311-data.md) | Ride share trips (Lyft, Uber etc) in NYC | | [`nyc.service_requests`](nyc-311-data.md) | Requests to NYC's 311 complaint hotline through phone and web | | [`hn.hacker_news`](hacker-news.md) | Sample of comments from [Hacker News](https://news.ycombinator.com/) | | [`kaggle.movies`](kaggle-movies.md) | Movie titles and overviews with pre-computed embeddings from [Kaggle](https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset) | | [`stackoverflow_survey.survey_results`](stackoverflow-survey.md) | Survey results from 2017 to 2024 | | [`stackoverflow_survey.survey_schemas`](stackoverflow-survey.md) | Survey schemas (questions from the survey) from 2017 to 2024 | ## Additional datasets The following datasets are available as separate shared databases. See each dataset's page for instructions on how to attach them. :::note[`aws-us-east-1` region only] These additional databases are only available for accounts in the `aws-us-east-1` region. ::: | Dataset | Description | |--------------------------------------------|---------------------------------------------------------------------------------------| | [StackOverflow](stackoverflow.md) | Full StackOverflow data dump up to May 2023 | | [PyPi / DuckDB Stats](pypi.md) | Python package download data for the `duckdb` package, refreshed weekly | | [Hacker News (full)](hacker-news.md) | Full [Hacker News](https://news.ycombinator.com/) dataset from 2016 to 2025 | | [Foursquare](foursquare.md) | Global dataset of over 100 million points of interest (POIs) with location and business information | ## FAQ ### How do I re-attach the sample_data database? The `sample_data` database is attached automatically, but if you have accidentally removed it, you can re-attach it. The `sample_data` share is [region-scoped](/concepts/architecture-and-capabilities/#the-motherduck-cloud-service), so use the share URL that matches your Organization's cloud region: | Tier | AWS Region | Share URL | |------|--------|-----------| | **Tier 1** | **US East (N. Virginia)** `us-east-1` | `md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6` | | **Tier 1** | **US West (Oregon)** `us-west-2` | `md:_share/sample_data/6b2babf0-bd16-465e-9243-f137a2e5b763` | | **Tier 2** | **Europe (Frankfurt)** `eu-central-1` | `md:_share/sample_data/ca7ad3fa-8709-4f9f-b7ec-b227b09d4ef2` | | **Tier 2** | **Europe (Dublin)** `eu-west-1` | `md:_share/sample_data/cec44d04-1b52-425d-9bcb-9be943d4c7b8` | | **Tier 3** | **Asia Pacific (Sydney)** `ap-southeast-2` | `md:_share/sample_data/0a065d32-d2ab-4662-8bcf-1f587d9d5916` | | **Tier 3** | **Asia Pacific (Tokyo)** `ap-northeast-1` | `md:_share/sample_data/6ae6172b-e9c8-4145-9f40-b19e36c97e4e` | For example, for an Organization in `eu-west-1`: ```sql ATTACH 'md:_share/sample_data/cec44d04-1b52-425d-9bcb-9be943d4c7b8' AS sample_data; ``` --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fgetting-started%2Fsample-data-queries%2F&page_title=MotherDuck%20Documentation%20-%20Example%20Datasets&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.