// Advanced Go with generics, interfaces, and concurrent programming
package main
import (
"context"
"encoding/json"
"errors"
"fmt"
"log"
"net/http"
"sync"
"time"
"github.com/google/uuid"
"golang.org/x/sync/errgroup"
)
// User represents a user in the system
type User struct {
ID uuid.UUID `json:"id" db:"id"`
Name string `json:"name" db:"name"`
Email string `json:"email" db:"email"`
Preferences UserPreferences `json:"preferences" db:"preferences"`
CreatedAt time.Time `json:"created_at" db:"created_at"`
UpdatedAt *time.Time `json:"updated_at,omitempty" db:"updated_at"`
Metadata map[string]any `json:"metadata,omitempty" db:"metadata"`
}
// UserPreferences contains user-specific settings
type UserPreferences struct {
Theme Theme `json:"theme" db:"theme"`
Language string `json:"language" db:"language"`
Notifications NotificationSettings `json:"notifications" db:"notifications"`
Privacy PrivacySettings `json:"privacy" db:"privacy"`
}
// Theme represents the user's preferred theme
type Theme string
const (
ThemeLight Theme = "light"
ThemeDark Theme = "dark"
ThemeAuto Theme = "auto"
)
// NotificationSettings controls notification preferences
type NotificationSettings struct {
Email bool `json:"email" db:"email"`
Push bool `json:"push" db:"push"`
SMS bool `json:"sms" db:"sms"`
}
// PrivacySettings controls privacy preferences
type PrivacySettings struct {
ProfileVisibility ProfileVisibility `json:"profile_visibility" db:"profile_visibility"`
DataSharing bool `json:"data_sharing" db:"data_sharing"`
}
// ProfileVisibility controls who can see the user's profile
type ProfileVisibility string
const (
ProfileVisibilityPublic ProfileVisibility = "public"
ProfileVisibilityPrivate ProfileVisibility = "private"
ProfileVisibilityFriends ProfileVisibility = "friends"
)
// APIResponse represents a standard API response
type APIResponse[T any] struct {
Data T `json:"data"`
Message string `json:"message"`
Success bool `json:"success"`
Timestamp int64 `json:"timestamp"`
Pagination *PaginationInfo `json:"pagination,omitempty"`
}
// PaginationInfo provides pagination metadata
type PaginationInfo struct {
Page int `json:"page"`
Limit int `json:"limit"`
Total int `json:"total"`
HasNext bool `json:"has_next"`
}
// Custom error types
var (
ErrUserNotFound = errors.New("user not found")
ErrInvalidEmail = errors.New("invalid email format")
ErrValidationError = errors.New("validation error")
)
// UserError represents a user-related error
type UserError struct {
Type string `json:"type"`
Message string `json:"message"`
Code int `json:"code"`
}
func (e UserError) Error() string {
return fmt.Sprintf("%s: %s", e.Type, e.Message)
}
// UserRepository defines the interface for user data operations
type UserRepository interface {
Create(ctx context.Context, user *User) error
GetByID(ctx context.Context, id uuid.UUID) (*User, error)
Update(ctx context.Context, id uuid.UUID, updates map[string]any) error
Delete(ctx context.Context, id uuid.UUID) error
List(ctx context.Context, pagination PaginationParams) ([]*User, error)
}
// PaginationParams defines pagination parameters
type PaginationParams struct {
Page int `json:"page"`
Limit int `json:"limit"`
}
// InMemoryUserRepository implements UserRepository using in-memory storage
type InMemoryUserRepository struct {
users map[uuid.UUID]*User
mutex sync.RWMutex
}
// NewInMemoryUserRepository creates a new in-memory user repository
func NewInMemoryUserRepository() *InMemoryUserRepository {
return &InMemoryUserRepository{
users: make(map[uuid.UUID]*User),
}
}
// Create adds a new user to the repository
func (r *InMemoryUserRepository) Create(ctx context.Context, user *User) error {
r.mutex.Lock()
defer r.mutex.Unlock()
if err := r.validateUser(user); err != nil {
return err
}
user.ID = uuid.New()
user.CreatedAt = time.Now()
r.users[user.ID] = user
return nil
}
// GetByID retrieves a user by their ID
func (r *InMemoryUserRepository) GetByID(ctx context.Context, id uuid.UUID) (*User, error) {
r.mutex.RLock()
defer r.mutex.RUnlock()
user, exists := r.users[id]
if !exists {
return nil, ErrUserNotFound
}
// Return a copy to prevent external modifications
userCopy := *user
return &userCopy, nil
}
// Update modifies an existing user
func (r *InMemoryUserRepository) Update(ctx context.Context, id uuid.UUID, updates map[string]any) error {
r.mutex.Lock()
defer r.mutex.Unlock()
user, exists := r.users[id]
if !exists {
return ErrUserNotFound
}
// Apply updates
for key, value := range updates {
switch key {
case "name":
if name, ok := value.(string); ok {
user.Name = name
}
case "email":
if email, ok := value.(string); ok {
if err := r.validateEmail(email); err != nil {
return err
}
user.Email = email
}
case "preferences":
if prefs, ok := value.(UserPreferences); ok {
user.Preferences = prefs
}
}
}
now := time.Now()
user.UpdatedAt = &now
return nil
}
// Delete removes a user from the repository
func (r *InMemoryUserRepository) Delete(ctx context.Context, id uuid.UUID) error {
r.mutex.Lock()
defer r.mutex.Unlock()
if _, exists := r.users[id]; !exists {
return ErrUserNotFound
}
delete(r.users, id)
return nil
}
// List retrieves users with pagination
func (r *InMemoryUserRepository) List(ctx context.Context, pagination PaginationParams) ([]*User, error) {
r.mutex.RLock()
defer r.mutex.RUnlock()
var users []*User
for _, user := range r.users {
users = append(users, user)
}
// Simple pagination
start := pagination.Page * pagination.Limit
end := start + pagination.Limit
if start >= len(users) {
return []*User{}, nil
}
if end > len(users) {
end = len(users)
}
return users[start:end], nil
}
// validateUser validates user data
func (r *InMemoryUserRepository) validateUser(user *User) error {
if user.Name == "" {
return fmt.Errorf("%w: name cannot be empty", ErrValidationError)
}
if err := r.validateEmail(user.Email); err != nil {
return err
}
return nil
}
// validateEmail validates email format
func (r *InMemoryUserRepository) validateEmail(email string) error {
if email == "" || !contains(email, "@") || !contains(email, ".") {
return fmt.Errorf("%w: %s", ErrInvalidEmail, email)
}
return nil
}
// contains checks if a string contains a substring
func contains(s, substr string) bool {
return len(s) >= len(substr) && (s == substr || len(substr) == 0 ||
(len(s) > len(substr) && (s[:len(substr)] == substr ||
s[len(s)-len(substr):] == substr ||
containsSubstring(s, substr))))
}
func containsSubstring(s, substr string) bool {
for i := 0; i <= len(s)-len(substr); i++ {
if s[i:i+len(substr)] == substr {
return true
}
}
return false
}
// UserService provides business logic for user operations
type UserService struct {
repo UserRepository
}
// NewUserService creates a new user service
func NewUserService(repo UserRepository) *UserService {
return &UserService{repo: repo}
}
// CreateUser creates a new user
func (s *UserService) CreateUser(ctx context.Context, req CreateUserRequest) (*APIResponse[User], error) {
user := &User{
Name: req.Name,
Email: req.Email,
Preferences: req.Preferences,
Metadata: make(map[string]any),
}
if err := s.repo.Create(ctx, user); err != nil {
return nil, err
}
return &APIResponse[User]{
Data: *user,
Message: "User created successfully",
Success: true,
Timestamp: time.Now().Unix(),
}, nil
}
// GetUser retrieves a user by ID
func (s *UserService) GetUser(ctx context.Context, id uuid.UUID) (*APIResponse[User], error) {
user, err := s.repo.GetByID(ctx, id)
if err != nil {
return nil, err
}
return &APIResponse[User]{
Data: *user,
Message: "User retrieved successfully",
Success: true,
Timestamp: time.Now().Unix(),
}, nil
}
// UpdateUser updates an existing user
func (s *UserService) UpdateUser(ctx context.Context, id uuid.UUID, req UpdateUserRequest) (*APIResponse[User], error) {
updates := make(map[string]any)
if req.Name != nil {
updates["name"] = *req.Name
}
if req.Email != nil {
updates["email"] = *req.Email
}
if req.Preferences != nil {
updates["preferences"] = *req.Preferences
}
if err := s.repo.Update(ctx, id, updates); err != nil {
return nil, err
}
user, err := s.repo.GetByID(ctx, id)
if err != nil {
return nil, err
}
return &APIResponse[User]{
Data: *user,
Message: "User updated successfully",
Success: true,
Timestamp: time.Now().Unix(),
}, nil
}
// DeleteUser removes a user
func (s *UserService) DeleteUser(ctx context.Context, id uuid.UUID) (*APIResponse[bool], error) {
if err := s.repo.Delete(ctx, id); err != nil {
return nil, err
}
return &APIResponse[bool]{
Data: true,
Message: "User deleted successfully",
Success: true,
Timestamp: time.Now().Unix(),
}, nil
}
// ListUsers retrieves users with pagination
func (s *UserService) ListUsers(ctx context.Context, pagination PaginationParams) (*APIResponse[[]*User], error) {
users, err := s.repo.List(ctx, pagination)
if err != nil {
return nil, err
}
return &APIResponse[[]*User]{
Data: users,
Message: "Users retrieved successfully",
Success: true,
Timestamp: time.Now().Unix(),
Pagination: &PaginationInfo{
Page: pagination.Page,
Limit: pagination.Limit,
Total: len(users),
HasNext: false, // Simplified for example
},
}, nil
}
// Request/Response types
type CreateUserRequest struct {
Name string `json:"name"`
Email string `json:"email"`
Preferences UserPreferences `json:"preferences"`
}
type UpdateUserRequest struct {
Name *string `json:"name,omitempty"`
Email *string `json:"email,omitempty"`
Preferences *UserPreferences `json:"preferences,omitempty"`
}
// ProcessUsersBatch processes multiple users concurrently
func (s *UserService) ProcessUsersBatch(ctx context.Context, userIDs []uuid.UUID) error {
g, ctx := errgroup.WithContext(ctx)
for _, id := range userIDs {
id := id // Capture loop variable
g.Go(func() error {
user, err := s.repo.GetByID(ctx, id)
if err != nil {
return err
}
// Simulate some processing
time.Sleep(10 * time.Millisecond)
log.Printf("Processed user: %s", user.Name)
return nil
})
}
return g.Wait()
}
func main() {
// Initialize repository and service
repo := NewInMemoryUserRepository()
service := NewUserService(repo)
ctx := context.Background()
// Create a user
createReq := CreateUserRequest{
Name: "John Doe",
Email: "john.doe@example.com",
Preferences: UserPreferences{
Theme: ThemeAuto,
Language: "en",
Notifications: NotificationSettings{
Email: true,
Push: false,
SMS: false,
},
Privacy: PrivacySettings{
ProfileVisibility: ProfileVisibilityPublic,
DataSharing: false,
},
},
}
response, err := service.CreateUser(ctx, createReq)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Created user: %+v\n", response.Data)
// Process multiple users concurrently
userIDs := []uuid.UUID{response.Data.ID}
if err := service.ProcessUsersBatch(ctx, userIDs); err != nil {
log.Fatal(err)
}
}
SQL
-- Advanced SQL with complex queries, window functions, and performance optimization
-- Database schema for a comprehensive user management system
-- Create database and set up extensions
CREATE DATABASE user_management;
\c user_management;
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
-- Create custom types
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending');
CREATE TYPE theme_type AS ENUM ('light', 'dark', 'auto');
CREATE TYPE profile_visibility AS ENUM ('public', 'private', 'friends');
CREATE TYPE notification_type AS ENUM ('email', 'push', 'sms');
-- Create main users table with comprehensive indexing
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
status user_status DEFAULT 'pending',
email_verified BOOLEAN DEFAULT FALSE,
last_login_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE NULL,
-- Constraints
CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT users_name_length CHECK (LENGTH(name) >= 2 AND LENGTH(name) <= 255)
);
-- Create user preferences table
CREATE TABLE user_preferences (
user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
theme theme_type DEFAULT 'auto',
language VARCHAR(10) DEFAULT 'en',
timezone VARCHAR(50) DEFAULT 'UTC',
date_format VARCHAR(20) DEFAULT 'YYYY-MM-DD',
currency VARCHAR(3) DEFAULT 'USD',
notifications JSONB DEFAULT '{"email": true, "push": false, "sms": false}',
privacy JSONB DEFAULT '{"profile_visibility": "public", "data_sharing": false}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create user metadata table for flexible data storage
CREATE TABLE user_metadata (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
key VARCHAR(100) NOT NULL,
value JSONB NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(user_id, key)
);
-- Create user sessions table
CREATE TABLE user_sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token_hash VARCHAR(255) NOT NULL,
ip_address INET,
user_agent TEXT,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
is_active BOOLEAN DEFAULT TRUE
);
-- Create user activity log table
CREATE TABLE user_activity_log (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
action VARCHAR(100) NOT NULL,
resource_type VARCHAR(50),
resource_id UUID,
metadata JSONB,
ip_address INET,
user_agent TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create comprehensive indexes for performance
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
CREATE INDEX CONCURRENTLY idx_users_name_gin ON users USING gin(name gin_trgm_ops);
CREATE INDEX CONCURRENTLY idx_users_email_gin ON users USING gin(email gin_trgm_ops);
CREATE INDEX CONCURRENTLY idx_user_metadata_user_id ON user_metadata(user_id);
CREATE INDEX CONCURRENTLY idx_user_metadata_key ON user_metadata(key);
CREATE INDEX CONCURRENTLY idx_user_metadata_value_gin ON user_metadata USING gin(value);
CREATE INDEX CONCURRENTLY idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX CONCURRENTLY idx_user_sessions_token_hash ON user_sessions(token_hash);
CREATE INDEX CONCURRENTLY idx_user_sessions_expires_at ON user_sessions(expires_at);
CREATE INDEX CONCURRENTLY idx_user_sessions_active ON user_sessions(is_active) WHERE is_active = TRUE;
CREATE INDEX CONCURRENTLY idx_user_activity_user_id ON user_activity_log(user_id);
CREATE INDEX CONCURRENTLY idx_user_activity_action ON user_activity_log(action);
CREATE INDEX CONCURRENTLY idx_user_activity_created_at ON user_activity_log(created_at);
CREATE INDEX CONCURRENTLY idx_user_activity_metadata_gin ON user_activity_log USING gin(metadata);
-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- Create triggers for updated_at
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_preferences_updated_at BEFORE UPDATE ON user_preferences
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_metadata_updated_at BEFORE UPDATE ON user_metadata
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- Insert sample data
INSERT INTO users (name, email, password_hash, status, email_verified) VALUES
('John Doe', 'john.doe@example.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4J/8KzKz2', 'active', TRUE),
('Jane Smith', 'jane.smith@example.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4J/8KzKz2', 'active', TRUE),
('Bob Johnson', 'bob.johnson@example.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4J/8KzKz2', 'inactive', FALSE),
('Alice Brown', 'alice.brown@example.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4J/8KzKz2', 'active', TRUE);
-- Insert user preferences
INSERT INTO user_preferences (user_id, theme, language, notifications, privacy)
SELECT
u.id,
CASE (ROW_NUMBER() OVER ()) % 3
WHEN 0 THEN 'light'::theme_type
WHEN 1 THEN 'dark'::theme_type
ELSE 'auto'::theme_type
END,
CASE (ROW_NUMBER() OVER ()) % 2
WHEN 0 THEN 'en'
ELSE 'es'
END,
jsonb_build_object(
'email', (ROW_NUMBER() OVER ()) % 2 = 0,
'push', (ROW_NUMBER() OVER ()) % 3 = 0,
'sms', FALSE
),
jsonb_build_object(
'profile_visibility',
CASE (ROW_NUMBER() OVER ()) % 3
WHEN 0 THEN 'public'
WHEN 1 THEN 'private'
ELSE 'friends'
END,
'data_sharing', (ROW_NUMBER() OVER ()) % 4 = 0
)
FROM users u;
-- Insert sample metadata
INSERT INTO user_metadata (user_id, key, value)
SELECT
u.id,
'avatar_url',
jsonb_build_object('url', 'https://example.com/avatars/' || u.id::text || '.jpg')
FROM users u;
-- Complex analytical queries
-- 1. User statistics with window functions
WITH user_stats AS (
SELECT
u.id,
u.name,
u.email,
u.status,
u.created_at,
up.theme,
up.language,
up.notifications->>'email' as email_notifications,
up.privacy->>'profile_visibility' as profile_visibility,
COUNT(ual.id) as activity_count,
MAX(ual.created_at) as last_activity,
ROW_NUMBER() OVER (PARTITION BY u.status ORDER BY u.created_at DESC) as status_rank,
RANK() OVER (ORDER BY u.created_at) as registration_rank,
LAG(u.created_at) OVER (ORDER BY u.created_at) as prev_user_created_at,
LEAD(u.created_at) OVER (ORDER BY u.created_at) as next_user_created_at
FROM users u
LEFT JOIN user_preferences up ON u.id = up.user_id
LEFT JOIN user_activity_log ual ON u.id = ual.user_id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name, u.email, u.status, u.created_at, up.theme, up.language, up.notifications, up.privacy
)
SELECT
status,
COUNT(*) as user_count,
AVG(activity_count) as avg_activity,
MIN(created_at) as first_registration,
MAX(created_at) as last_registration,
COUNT(*) FILTER (WHERE email_notifications = 'true') as email_notifications_enabled,
COUNT(*) FILTER (WHERE profile_visibility = 'public') as public_profiles
FROM user_stats
GROUP BY status
ORDER BY user_count DESC;
-- 2. Advanced user search with full-text search
SELECT
u.id,
u.name,
u.email,
u.status,
up.theme,
up.language,
ts_rank(
to_tsvector('english', u.name || ' ' || u.email),
plainto_tsquery('english', 'john')
) as relevance_score
FROM users u
LEFT JOIN user_preferences up ON u.id = up.user_id
WHERE
to_tsvector('english', u.name || ' ' || u.email) @@ plainto_tsquery('english', 'john')
OR u.name ILIKE '%john%'
OR u.email ILIKE '%john%'
ORDER BY relevance_score DESC, u.created_at DESC;
-- 3. User activity analysis with time-based aggregations
SELECT
DATE_TRUNC('day', ual.created_at) as activity_date,
ual.action,
COUNT(*) as action_count,
COUNT(DISTINCT ual.user_id) as unique_users,
AVG(COUNT(*)) OVER (
PARTITION BY ual.action
ORDER BY DATE_TRUNC('day', ual.created_at)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as seven_day_avg
FROM user_activity_log ual
WHERE ual.created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', ual.created_at), ual.action
ORDER BY activity_date DESC, action_count DESC;
-- 4. User engagement metrics with complex CTEs
WITH user_engagement AS (
SELECT
u.id,
u.name,
u.email,
u.created_at as registration_date,
COUNT(ual.id) as total_activities,
COUNT(DISTINCT DATE(ual.created_at)) as active_days,
MAX(ual.created_at) as last_activity,
MIN(ual.created_at) as first_activity,
EXTRACT(DAYS FROM (MAX(ual.created_at) - MIN(ual.created_at))) as activity_span_days,
COUNT(us.id) as session_count,
MAX(us.last_accessed_at) as last_session
FROM users u
LEFT JOIN user_activity_log ual ON u.id = ual.user_id
LEFT JOIN user_sessions us ON u.id = us.user_id AND us.is_active = TRUE
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name, u.email, u.created_at
),
engagement_metrics AS (
SELECT
*,
CASE
WHEN total_activities = 0 THEN 'inactive'
WHEN total_activities < 10 THEN 'low'
WHEN total_activities < 50 THEN 'medium'
ELSE 'high'
END as engagement_level,
CASE
WHEN last_activity IS NULL THEN NULL
WHEN last_activity > NOW() - INTERVAL '7 days' THEN 'active'
WHEN last_activity > NOW() - INTERVAL '30 days' THEN 'recent'
ELSE 'dormant'
END as recency_level
FROM user_engagement
)
SELECT
engagement_level,
recency_level,
COUNT(*) as user_count,
AVG(total_activities) as avg_activities,
AVG(active_days) as avg_active_days,
AVG(session_count) as avg_sessions
FROM engagement_metrics
GROUP BY engagement_level, recency_level
ORDER BY engagement_level, recency_level;
-- 5. Performance monitoring query
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT
u.id,
u.name,
u.email,
up.theme,
up.notifications,
COUNT(ual.id) as activity_count,
MAX(ual.created_at) as last_activity
FROM users u
INNER JOIN user_preferences up ON u.id = up.user_id
LEFT JOIN user_activity_log ual ON u.id = ual.user_id
WHERE u.status = 'active'
AND u.created_at >= NOW() - INTERVAL '90 days'
AND up.notifications->>'email' = 'true'
GROUP BY u.id, u.name, u.email, up.theme, up.notifications
HAVING COUNT(ual.id) > 5
ORDER BY last_activity DESC NULLS LAST
LIMIT 100;