112 lines
2.3 KiB
SQL
112 lines
2.3 KiB
SQL
-- ======================================================
|
|
-- SQL QUERIES TO CHECK REGISTERED USERS
|
|
-- ======================================================
|
|
|
|
-- 1. BASIC QUERY: All Registered Users (excluding deleted)
|
|
SELECT
|
|
id,
|
|
phone_number,
|
|
name,
|
|
country_code,
|
|
user_type,
|
|
active_role,
|
|
is_active,
|
|
created_at,
|
|
last_login_at
|
|
FROM users
|
|
WHERE deleted = FALSE
|
|
ORDER BY created_at DESC;
|
|
|
|
-- 2. DETAILED QUERY: All User Information
|
|
SELECT
|
|
id,
|
|
phone_number,
|
|
name,
|
|
country_code,
|
|
user_type,
|
|
active_role,
|
|
roles,
|
|
is_active,
|
|
rating_average,
|
|
rating_count,
|
|
subscription_plan_id,
|
|
subscription_expires_at,
|
|
created_at,
|
|
updated_at,
|
|
last_login_at
|
|
FROM users
|
|
WHERE deleted = FALSE
|
|
ORDER BY created_at DESC;
|
|
|
|
-- 3. ACTIVE USERS ONLY
|
|
SELECT
|
|
id,
|
|
phone_number,
|
|
name,
|
|
country_code,
|
|
user_type,
|
|
active_role,
|
|
created_at,
|
|
last_login_at
|
|
FROM users
|
|
WHERE deleted = FALSE
|
|
AND is_active = TRUE
|
|
ORDER BY last_login_at DESC NULLS LAST;
|
|
|
|
-- 4. USER COUNT SUMMARY
|
|
SELECT
|
|
COUNT(*) as total_users,
|
|
COUNT(*) FILTER (WHERE is_active = TRUE) as active_users,
|
|
COUNT(*) FILTER (WHERE is_active = FALSE) as inactive_users,
|
|
COUNT(*) FILTER (WHERE deleted = TRUE) as deleted_users,
|
|
COUNT(*) FILTER (WHERE subscription_plan_id IS NOT NULL) as subscribed_users
|
|
FROM users;
|
|
|
|
-- 5. RECENT REGISTRATIONS (Last 30 days)
|
|
SELECT
|
|
id,
|
|
phone_number,
|
|
name,
|
|
country_code,
|
|
user_type,
|
|
active_role,
|
|
created_at
|
|
FROM users
|
|
WHERE deleted = FALSE
|
|
AND created_at >= NOW() - INTERVAL '30 days'
|
|
ORDER BY created_at DESC;
|
|
|
|
-- 6. USERS WITH SUBSCRIPTION INFO
|
|
SELECT
|
|
u.id,
|
|
u.phone_number,
|
|
u.name,
|
|
u.country_code,
|
|
sp.name as subscription_plan_name,
|
|
u.subscription_expires_at,
|
|
CASE
|
|
WHEN u.subscription_expires_at > NOW() THEN 'Active'
|
|
ELSE 'Expired'
|
|
END as subscription_status
|
|
FROM users u
|
|
LEFT JOIN subscription_plans sp ON u.subscription_plan_id = sp.id
|
|
WHERE u.deleted = FALSE
|
|
ORDER BY u.created_at DESC;
|
|
|
|
-- 7. SEARCH USER BY PHONE NUMBER
|
|
-- Replace 'YOUR_PHONE_NUMBER' with the actual phone number
|
|
SELECT
|
|
id,
|
|
phone_number,
|
|
name,
|
|
country_code,
|
|
user_type,
|
|
active_role,
|
|
is_active,
|
|
created_at,
|
|
last_login_at
|
|
FROM users
|
|
WHERE phone_number = 'YOUR_PHONE_NUMBER'
|
|
AND deleted = FALSE;
|
|
|