Intermediate SQL

SELECT COUNT(*) AS total_sessions,
       SUM(clicks) AS total_clicks,
       AVG(time_spent_minutes) AS avg_time
FROM user_engagement;  -- Aggregates data to get session count, total clicks, and average time spent

SELECT MIN(time_spent_minutes) AS min_time, 
       MAX(time_spent_minutes) AS max_time
FROM user_engagement;  -- Shows the minimum and maximum time spent

SELECT COUNT(*) FROM user_engagement;  -- Counts all rows in the table

SELECT SUM(clicks) FROM user_engagement;  -- Calculates total number of clicks

SELECT AVG(time_spent_minutes) FROM user_engagement;  -- Finds average time spent per session

SELECT * FROM user_engagement 
WHERE region LIKE 'A%';  -- Selects rows where region starts with 'A'

SELECT * FROM user_engagement 
WHERE region LIKE '_sia';  -- Matches regions like Asia using underscore wildcard

SELECT * FROM user_engagement 
WHERE region IN ('Europe', 'Asia');  -- Filters for users in either Europe or Asia

SELECT * FROM user_engagement 
WHERE time_spent_minutes BETWEEN 10 AND 30;  -- Returns users who spent between 10 to 30 minutes

SELECT user_id AS id, region AS location 
FROM user_engagement;  -- Renames user_id to id and region to location in the result

SELECT u.user_id, u.name, e.time_spent_minutes 
FROM users u
JOIN user_engagement e ON u.user_id = e.user_id;  -- Joins users with engagement data

SELECT * FROM users u
INNER JOIN user_engagement e ON u.user_id = e.user_id;  -- Only shows matching users from both tables

SELECT * FROM users u
LEFT JOIN user_engagement e ON u.user_id = e.user_id;  -- Shows all users even if they have no engagement

SELECT * FROM users u
RIGHT JOIN user_engagement e ON u.user_id = e.user_id;  -- Shows all engagement records even if user data is missing

SELECT * FROM users u
FULL OUTER JOIN user_engagement e ON u.user_id = e.user_id;  -- Includes all records from both tables

SELECT a.user_id, b.user_id, a.region 
FROM user_engagement a
JOIN user_engagement b ON a.region = b.region AND a.user_id <> b.user_id;  -- Pairs users from the same region

SELECT region FROM user_engagement 
UNION 
SELECT region FROM archived_engagement;  -- Merges region data from two tables without duplicates

SELECT region, COUNT(*) AS sessions 
FROM user_engagement
GROUP BY region;  -- Aggregates session counts by region

SELECT region, COUNT(*) AS sessions 
FROM user_engagement
GROUP BY region
HAVING COUNT(*) > 5;  -- Shows only regions with more than 5 sessions

SELECT * FROM user_engagement e
WHERE EXISTS (
  SELECT 1 FROM users u WHERE u.user_id = e.user_id
);  -- Filters engagement records where corresponding user exists

SELECT * FROM user_engagement
WHERE clicks > ANY (
  SELECT clicks FROM user_engagement WHERE region = 'Europe'
);  -- Returns users with clicks greater than any user from Europe

SELECT * INTO engagement_backup 
FROM user_engagement;  -- Creates a backup copy of the table

INSERT INTO engagement_backup
SELECT * FROM user_engagement;  -- Copies data from user_engagement into engagement_backup

SELECT user_id,
  CASE
    WHEN time_spent_minutes > 30 THEN 'High'
    WHEN time_spent_minutes BETWEEN 15 AND 30 THEN 'Medium'
    ELSE 'Low'
  END AS engagement_level
FROM user_engagement;  -- Categorizes users by engagement level

SELECT COALESCE(time_spent_minutes, 0) AS time_spent 
FROM user_engagement;  -- Replaces NULL time values with 0

CREATE OR REPLACE FUNCTION get_user_sessions(userId INT)
RETURNS TABLE(session_date DATE, time_spent INT) AS $$
BEGIN
  RETURN QUERY
  SELECT session_date, time_spent_minutes 
  FROM user_engagement 
  WHERE user_id = userId;
END;
$$ LANGUAGE plpgsql;  -- A stored function to retrieve sessions for a specific user

-- This is a single-line comment
SELECT * FROM user_engagement;  -- another single-line comment
 
/*
  This is a multi-line
  SQL comment
*/  -- Comments for documentation or explanation

SELECT * FROM user_engagement 
WHERE time_spent_minutes > 20 AND clicks <= 30;  -- Uses comparison and logical operators