---
sidebar_position: 0.9
title: PROMPT_EXPLAIN
description: Get AI-generated explanations of SQL queries using the PROMPT_EXPLAIN function.
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';

## Explain a query

The `prompt_explain` table function allows MotherDuck AI to analyze and explain SQL queries in plain English. This feature helps you understand complex queries, verify that a query does what you intend, and learn SQL concepts through practical examples.

::::tip
This function is particularly useful for understanding queries written by others or for automatically documenting your own queries for future reference.
::::

### Syntax

```sql
CALL prompt_explain('<SQL query>', [include_tables=['<table_name>', '<table_name>']]);
```

### Parameters

| **Parameter**      | **Required** | **Description**                                                                                                          |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query`            | Yes          | The SQL query to explain                                                                                                 |
| `include_tables`   | No           | Array of table names to consider for context (defaults to all tables in current database). Can also be a dictionary in the format `{'table_name': ['column1', 'column2']}` to specify which columns to include for each table. |

### Example usage

Here are several examples using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).

#### Explaining a complex query

```sql
CALL prompt_explain('
SELECT COUNT(*) as domain_count, 
SUBSTRING(SPLIT_PART(url, ''//'', 2), 1, POSITION(''/'' IN SPLIT_PART(url, ''//'', 2)) - 1) as domain 
FROM hn.hacker_news
WHERE url IS NOT NULL GROUP BY domain ORDER BY domain_count DESC LIMIT 10;
');
```

**Output**: when you run a `prompt_explain` query, you'll receive a single-column table with a detailed explanation:

| **explanation** |
|-----------------|
|The query retrieves the top 10 most frequent domains from the `url` field in the `hn.hacker_news` table. It counts the occurrences of each domain by extracting the domain part from the URL (after the '//' and before the next '/'), groups the results by domain, and orders them in descending order of their count. The result includes the count of occurrences (`domain_count`) and the domain name itself (`domain`). |

#### Using dictionary format for include_tables

You can specify which columns to include for each table using the dictionary format:

```sql
CALL prompt_explain('
SELECT u.id, u.name, COUNT(s.id) AS story_count
FROM hn.users u
LEFT JOIN hn.stories s ON u.id = s.user_id
GROUP BY u.id, u.name
HAVING COUNT(s.id) > 5
ORDER BY story_count DESC
LIMIT 20;
', include_tables={'hn.users': ['id', 'name'], 'hn.stories': ['id', 'user_id']});
```

This approach allows you to focus the explanation on only the relevant columns, which can be helpful for tables with many columns.

#### How it works

The `prompt_explain` function processes your query in several steps:

1. **Parsing**: analyzes the SQL syntax to understand the query structure
2. **Schema analysis**: examines the referenced tables and columns to understand the data model
3. **Operation analysis**: identifies the operations being performed (filtering, joining, aggregating, etc.)
4. **Translation**: converts the technical SQL into a clear, human-readable explanation
5. **Context addition**: adds relevant context about the purpose and expected results of the query

### Best practices

For the best results with `prompt_explain`:

1. **Provide complete queries**: include all parts of the query for the most accurate explanation
2. **Use table aliases consistently**: this helps the function understand table relationships
3. **Specify relevant tables**: use the `include_tables` parameter for large databases
4. **Review explanations**: verify that the explanation matches your understanding of the query
5. **Use for documentation**: save explanations as comments in your code for future reference



---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `POST https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

Payload:

```json
{
  "page_path": "/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-explain/",
  "page_title": "PROMPT_EXPLAIN",
  "text": "<the user's feedback, max 2000 characters>",
  "source": "<optional identifier for your interface, for example 'claude.ai' or 'chatgpt'>"
}
```

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed payloads, and `429` when rate-limited.