Detailed Breakdown of Solution Using Unnest, String_Agg, Split_Part and CTEs [PostgreSQL]
IntuitionThis query takes a table of user content, splits the content_text field into words, capitalizes each word according to specific rules (handling hyphena
# Intuition

This query takes a table of user content, splits the `content_text` field into words, capitalizes each word according to specific rules (handling hyphenated words appropriately), and reconstructs the content with the transformed words.

---

### Approach

#### **1. `word_table` CTE**
This Common Table Expression (CTE) breaks each row of `content_text` into individual words.

- **`string_to_array(content_text, ' ')`**: Splits the text into an array of words using a space (`' '`) as the delimiter.
- **`unnest()`**: Expands the resulting array so that each word appears as a separate row.

**Output**:
A table with columns:
- `content_id`: Identifier for the content.
- `words`: Each individual word from `content_text`.

```sql
SELECT content_id,
unnest(string_to_array(content_text, ' ')) words
FROM user_content
```

---

#### **2. `capitalized_words` CTE**
This CTE processes the `words` column from `word_table` to capitalize them according to specific rules.

##### **Logic**:
1. **If the word has no hyphen (`-`)**:
   - Extract the first character and capitalize it: `UPPER(LEFT(SPLIT_PART(words, '-', 1), 1))`.
   - Convert the rest of the word to lowercase: `LOWER(RIGHT(SPLIT_PART(words, '-', 1), LENGTH(SPLIT_PART(words, '-', 1)) - 1))`.

2. **If the word has a hyphen (`-`)**:
   - Capitalize both parts of the hyphenated word separately:
     - First part: Same logic as above.
     - Second part: Similar logic applied to the text after the hyphen.

3. **Reconstruct the word**:
   - Combine the two parts (if hyphenated) using a hyphen.

**Output**:
A table with columns:
- `content_id`: Content identifier.
- `words`: The capitalized version of each word.

```sql
SELECT content_id, CASE
WHEN SPLIT_PART(words, '-', 2) = '' THEN -- Not hyphenated
    UPPER(LEFT(SPLIT_PART(words, '-', 1), 1)) || LOWER(RIGHT(SPLIT_PART(words, '-', 1), LENGTH(SPLIT_PART(words, '-', 1)) - 1))
ELSE -- Hyphenated
    UPPER(LEFT(SPLIT_PART(words, '-', 1), 1)) || LOWER(RIGHT(SPLIT_PART(words, '-', 1), LENGTH(SPLIT_PART(words, '-', 1)) - 1)) 
    || '-' || 
    UPPER(LEFT(SPLIT_PART(words, '-', 2), 1)) || LOWER(RIGHT(SPLIT_PART(words, '-', 2), LENGTH(SPLIT_PART(words, '-', 2)) - 1))
END AS words 
FROM word_table
```

---

#### **3. Main Query**
The main query reconstructs the transformed `content_text` by aggregating the capitalized words for each `content_id`.

##### **Key Components**:
1. **`STRING_AGG(cw.words, ' ')`**:
   - Combines the processed words (`cw.words`) into a single string, using spaces as separators.

2. **`INNER JOIN user_content uc USING(content_id)`**:
   - Links the original `content_text` to the transformed text for comparison.

3. **`GROUP BY cw.content_id, content_text`**:
   - Groups by `content_id` to ensure one result per piece of content.

**Output**:
A table with columns:
- `content_id`: Identifier for the content.
- `original_text`: The original `content_text`.
- `converted_text`: The transformed version of `content_text` with capitalized words.

```sql
SELECT cw.content_id, content_text AS original_text, STRING_AGG(cw.words, ' ') converted_text
FROM capitalized_words cw
INNER JOIN user_content uc USING(content_id)
GROUP BY cw.content_id, content_text
```

---

# Code
```postgresql []
-- Write your PostgreSQL query statement below
WITH word_table AS 
(SELECT content_id,
unnest(string_to_array(content_text, ' ' )) words
FROM user_content), 
capitalized_words AS 
(SELECT content_id, CASE
WHEN SPLIT_PART(words, '-', 2) = '' THEN UPPER(LEFT(SPLIT_PART(words, '-', 1), 1)) || LOWER(RIGHT(SPLIT_PART(words, '-', 1), LENGTH(SPLIT_PART(words, '-', 1)) - 1))
ELSE UPPER(LEFT(SPLIT_PART(words, '-', 1), 1)) || LOWER(RIGHT(SPLIT_PART(words, '-', 1), LENGTH(SPLIT_PART(words, '-', 1)) - 1)) || '-' || UPPER(LEFT(SPLIT_PART(words, '-', 2), 1)) || LOWER(RIGHT(SPLIT_PART(words, '-', 2), LENGTH(SPLIT_PART(words, '-', 2)) - 1))
END AS words 
FROM word_table)

SELECT cw.content_id, content_text AS original_text, STRING_agg(cw.words, ' ') converted_text
FROM capitalized_words cw
INNER JOIN user_content uc USING(content_id)
GROUP BY cw.content_id, content_text
```
['PostgreSQL']