Data Science. SQL hub

🧠 SQL-задача с подвохом: "Невидимые дубликаты"

В таблице users хранятся email-адреса пользователей. Некоторые юзеры регистрируются повторно, маскируя один и тот же email по-разному:

| id | name | email |
|----|----------|--------------------------|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Charlie | [email protected] |
| 4 | Dave | [email protected] |
| 5 | Eve | [email protected] |


🎯 Цель:
Найти количество уникальных пользователей, если:
- Регистр не учитывается (`alice` = `ALICE`)
- Пробелы игнорируются
- Для @gmail.com:
— Убираются точки в имени
— Всё после + отрезается

✅ SQL-решение:

SELECT COUNT(DISTINCT normalized_email) AS unique_users
FROM (
    SELECT
      CASE
        WHEN email ILIKE '%@gmail.com' THEN
          REGEXP_REPLACE(
            SPLIT_PART(SPLIT_PART(LOWER(TRIM(email)), '+', 1), '@', 1),
            '\.', '', 'g'
          ) || '@gmail.com'
        ELSE
          LOWER(REPLACE(TRIM(email), ' ', ''))
      END AS normalized_email
    FROM users
) AS cleaned;

🔍 Как это работает:

LOWER(TRIM(email)) — убираем пробелы и регистр

SPLIT_PART(..., '+', 1) — отрезаем всё после +

REGEXP_REPLACE(..., '\.', '', 'g') — удаляем точки

Считаем DISTINCT, чтобы получить число уникальных email'ов

🔥 Используй такие трюки для:
• антифрода
• чистки базы
• аналитики поведения пользователей

#SQL #PostgreSQL #Gmail #EmailNormalization #DevTools #AntiFraud #DataCleaning #Analytics

👍10❤3

www.group-telegram.com/cn/sqlhub.com/1904

4.34K viewsJun 5 at 14:12

🧠 SQL-задача с подвохом: "Невидимые дубликаты"

В таблице users хранятся email-адреса пользователей. Некоторые юзеры регистрируются повторно, маскируя один и тот же email по-разному:

| id | name | email |
|----|----------|--------------------------|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Charlie | [email protected] |
| 4 | Dave | [email protected] |
| 5 | Eve | [email protected] |


🎯 Цель:
Найти количество уникальных пользователей, если:
- Регистр не учитывается (`alice` = `ALICE`)
- Пробелы игнорируются
- Для @gmail.com:
— Убираются точки в имени
— Всё после + отрезается

✅ SQL-решение:

SELECT COUNT(DISTINCT normalized_email) AS unique_users
FROM (
    SELECT
      CASE
        WHEN email ILIKE '%@gmail.com' THEN
          REGEXP_REPLACE(
            SPLIT_PART(SPLIT_PART(LOWER(TRIM(email)), '+', 1), '@', 1),
            '\.', '', 'g'
          ) || '@gmail.com'
        ELSE
          LOWER(REPLACE(TRIM(email), ' ', ''))
      END AS normalized_email
    FROM users
) AS cleaned;

BY Data Science. SQL hub