🎯 Цель: Найти количество уникальных пользователей, если: - Регистр не учитывается (`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'ов
🔥 Используй такие трюки для: • антифрода • чистки базы • аналитики поведения пользователей
READ MORE As a result, the pandemic saw many newcomers to Telegram, including prominent anti-vaccine activists who used the app's hands-off approach to share false information on shots, a study from the Institute for Strategic Dialogue shows. Channels are not fully encrypted, end-to-end. All communications on a Telegram channel can be seen by anyone on the channel and are also visible to Telegram. Telegram may be asked by a government to hand over the communications from a channel. Telegram has a history of standing up to Russian government requests for data, but how comfortable you are relying on that history to predict future behavior is up to you. Because Telegram has this data, it may also be stolen by hackers or leaked by an internal employee. At the start of 2018, the company attempted to launch an Initial Coin Offering (ICO) which would enable it to enable payments (and earn the cash that comes from doing so). The initial signals were promising, especially given Telegram’s user base is already fairly crypto-savvy. It raised an initial tranche of cash – worth more than a billion dollars – to help develop the coin before opening sales to the public. Unfortunately, third-party sales of coins bought in those initial fundraising rounds raised the ire of the SEC, which brought the hammer down on the whole operation. In 2020, officials ordered Telegram to pay a fine of $18.5 million and hand back much of the cash that it had raised. The SC urges the public to refer to the SC’s I nvestor Alert List before investing. The list contains details of unauthorised websites, investment products, companies and individuals. Members of the public who suspect that they have been approached by unauthorised firms or individuals offering schemes that promise unrealistic returns
from kr
