SELECT 
    u.id,
    u.name,
    u.email,
    u.role,
    COUNT(p.id) as processed_files,
    AVG(p.compression_rate) as avg_compression,
    MAX(p.created_at) as last_processed
FROM 
    users u
LEFT JOIN 
    processed_files p ON u.id = p.user_id
WHERE 
    u.active = 1
    AND u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY 
    u.id, u.name, u.email, u.role
HAVING 
    COUNT(p.id) > 0
ORDER BY 
    processed_files DESC,
    avg_compression ASC
LIMIT 100;

-- Create sample table
CREATE TABLE IF NOT EXISTS trinity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    file_size INT,
    compression_rate DECIMAL(5,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Insert sample data
INSERT INTO trinity_logs (user_id, action, file_size, compression_rate)
VALUES 
    (1, 'swap', 102400, 28.50),
    (2, 'restore', 71680, 0.00),
    (1, 'search', 102400, 28.50);
