🎯 Цель: Найти количество уникальных пользователей, если: - Регистр не учитывается (`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'ов
🔥 Используй такие трюки для: • антифрода • чистки базы • аналитики поведения пользователей
🎯 Цель: Найти количество уникальных пользователей, если: - Регистр не учитывается (`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'ов
🔥 Используй такие трюки для: • антифрода • чистки базы • аналитики поведения пользователей
Telegram auto-delete message, expiring invites, and more
elegram is updating its messaging app with options for auto-deleting messages, expiring invite links, and new unlimited groups, the company shared in a blog post. Much like Signal, Telegram received a burst of new users in the confusion over WhatsApp’s privacy policy and now the company is adopting features that were already part of its competitors’ apps, features which offer more security and privacy. Auto-deleting messages were already possible in Telegram’s encrypted Secret Chats, but this new update for iOS and Android adds the option to make messages disappear in any kind of chat. Auto-delete can be enabled inside of chats, and set to delete either 24 hours or seven days after messages are sent. Auto-delete won’t remove every message though; if a message was sent before the feature was turned on, it’ll stick around. Telegram’s competitors have had similar features: WhatsApp introduced a feature in 2020 and Signal has had disappearing messages since at least 2016.
Among the actives, Ascendas REIT sank 0.64 percent, while CapitaLand Integrated Commercial Trust plummeted 1.42 percent, City Developments plunged 1.12 percent, Dairy Farm International tumbled 0.86 percent, DBS Group skidded 0.68 percent, Genting Singapore retreated 0.67 percent, Hongkong Land climbed 1.30 percent, Mapletree Commercial Trust lost 0.47 percent, Mapletree Logistics Trust tanked 0.95 percent, Oversea-Chinese Banking Corporation dropped 0.61 percent, SATS rose 0.24 percent, SembCorp Industries shed 0.54 percent, Singapore Airlines surrendered 0.79 percent, Singapore Exchange slid 0.30 percent, Singapore Press Holdings declined 1.03 percent, Singapore Technologies Engineering dipped 0.26 percent, SingTel advanced 0.81 percent, United Overseas Bank fell 0.39 percent, Wilmar International eased 0.24 percent, Yangzijiang Shipbuilding jumped 1.42 percent and Keppel Corp, Thai Beverage, CapitaLand and Comfort DelGro were unchanged.
