Finding the Minimum Numeric Value

The most straightforward use is finding the smallest number in a column.

-- Find the lowest view count achieved by any video on TokTuk
SELECT MIN(views) AS lowest_video_views
FROM Videos;
-- Result: 500 (from the 'Cooking Tips' video)

Result: A single value representing the lowest view count across all videos (500 views).

Finding the Earliest Date/Timestamp

MIN is very useful for finding the earliest date or time in a dataset.

-- Find the date the first user joined the platform
SELECT MIN(join_date) AS earliest_signup_date
FROM Users;
-- Result: '2023-01-01' (alice's join date)

-- Find the timestamp of the first interaction
SELECT MIN(timestamp) AS first_interaction
FROM Interactions;
-- Result: '2023-02-01' (first likes on 'Funny Cat' video)

Result: The earliest date or timestamp value present in the respective column. In our data, the first user joined on January 1st, 2023.

Finding the Minimum String Value

When used with text or string columns, MIN returns the value that comes first in alphabetical (or dictionary) order, according to the database's collation rules.

-- Find the username that comes first alphabetically
SELECT MIN(username) AS first_alphabetical_username
FROM Users;
-- Result: 'alice'

Result: The username "alice" is returned as it comes first alphabetically among all usernames in our dataset.

MIN and NULL Value Handling

Similar to SUM and AVG, the MIN function ignores NULL values when determining the minimum. If all values considered are NULL, MIN returns NULL.

-- MIN ignores NULLs
-- If views are [100, 500, NULL, 200], MIN(views) returns 100.
SELECT MIN(views) AS min_views
FROM Videos;

Method 1: Using a Subquery in the WHERE Clause

This is the most common and intuitive method. First, find the minimum value using a subquery, then select rows where the column equals that minimum value.

-- Find the video(s) with the lowest view count
SELECT video_id, title, user_id, views
FROM Videos
WHERE views = (SELECT MIN(views) FROM Videos);
-- Result: video_id: 3, title: 'Cooking Tips', user_id: 1, views: 500

This returns all columns for the video with the lowest views (500), which is the "Cooking Tips" video by user 1 (alice).

Method 2: Using Window Functions (Often More Efficient)

Window functions like RANK() or DENSE_RANK() can identify the bottom record(s) without a separate subquery for the MIN value.

-- Find the least viewed video(s) using RANK
WITH RankedVideos AS (
  SELECT
    video_id, title, user_id, views,
    RANK() OVER (ORDER BY views ASC) as rnk -- Assign rank based on views
  FROM Videos
)
SELECT video_id, title, user_id, views
FROM RankedVideos
WHERE rnk = 1; -- Select rows with the bottom rank (rank 1)

This approach can be more efficient, especially if you need the bottom N records, and avoids calculating MIN separately. RANK() handles ties by giving them the same rank and skipping subsequent ranks; DENSE_RANK() also gives ties the same rank but doesn't skip ranks.

Method 3: Using ORDER BY and LIMIT (Simple for Bottom 1)

If you only need one row containing the minimum value (and don't care about ties), sorting and limiting is the simplest way.

-- Find *one* video with the lowest view count
SELECT video_id, title, user_id, views
FROM Videos
ORDER BY views ASC -- Sort by views ascending
LIMIT 1; -- Take only the first row

Note: If there's a tie for the minimum value, this method arbitrarily picks only one of the bottom rows. Use methods 1 or 2 if you need all tied rows.

Finding Minimums within Categories

Determine the lowest value for each group.

-- Find the lowest view count for videos uploaded by each user
SELECT
  u.username,
  MIN(v.views) AS lowest_views_for_user
FROM Users u
LEFT JOIN Videos v ON u.user_id = v.user_id
GROUP BY u.user_id, u.username
ORDER BY lowest_views_for_user ASC NULLS LAST;
-- Results include:
-- alice: 500 (Cooking Tips)
-- bob: 2000 (Dance Challenge)
-- charlie: 1500 (Travel Vlog)
-- etc.

This shows the lowest performing video for each user who has uploaded content.

Finding the Earliest Event per Category

Identify the first occurrence within each group.

-- Find the first interaction timestamp for each video
SELECT
  v.title,
  MIN(i.timestamp) AS first_interaction_time
FROM Videos v
LEFT JOIN Interactions i ON v.video_id = i.video_id
GROUP BY v.video_id, v.title
ORDER BY first_interaction_time ASC;
-- Results include:
-- 'Funny Cat': '2023-02-01'
-- 'Dance Challenge': '2023-02-02'
-- etc.

This helps identify when each video first received engagement activity, starting with the earliest interactions on February 1st, 2023.

Combining MIN with Other Aggregates per Group

Get a fuller picture by calculating multiple aggregate metrics for each group.

-- Show each user's video performance statistics
SELECT
  u.username,
  COUNT(v.video_id) AS video_count,
  MIN(v.views) AS min_views,
  MAX(v.views) AS max_views,
  AVG(v.views) AS avg_views
FROM Users u
LEFT JOIN Videos v ON u.user_id = v.user_id
GROUP BY u.user_id, u.username
HAVING COUNT(v.video_id) > 0
ORDER BY min_views ASC;
-- Results include:
-- alice: 2 videos, min: 500, max: 1000, avg: 750
-- bob: 1 video, min: 2000, max: 2000, avg: 2000
-- etc.

This provides a complete performance overview for each content creator, showing their video count and view statistics.

Filtering Groups Based on MIN (HAVING Clause)

Select only groups where the minimum value meets a certain condition.

-- Find users whose worst video still has over 1,000 views
SELECT
  u.username,
  MIN(v.views) AS min_views
FROM Users u
JOIN Videos v ON u.user_id = v.user_id
GROUP BY u.user_id, u.username
HAVING MIN(v.views) > 1000 -- Filter groups based on the min value found
ORDER BY min_views ASC;

Finding the Nth Minimum Value

Finding values other than the absolute minimum often involves window functions or clever subqueries.

-- Find the 2nd lowest view count using subquery
SELECT MIN(views)
FROM Videos
WHERE views > (SELECT MIN(views) FROM Videos);

-- Find the 3rd lowest view count using window functions (more general)
WITH RankedViews AS (
  SELECT
    views,
    DENSE_RANK() OVER (ORDER BY views ASC) as rnk
  FROM Videos
)
SELECT DISTINCT views
FROM RankedViews
WHERE rnk = 3;

The window function approach using DENSE_RANK() is generally more flexible for finding the Nth value and handles ties gracefully.

Finding the Row with MIN Value per Group (Least-N-Per-Group)

This is a common, slightly complex task: retrieve the full row corresponding to the minimum value within each group (e.g., find each user's least viewed video details). Window functions are often the most efficient solution.

-- Find details of each user's least viewed video using ROW_NUMBER()
WITH RankedUserVideos AS (
  SELECT
    v.video_id, v.title, v.user_id, v.views, v.upload_date,
    ROW_NUMBER() OVER (PARTITION BY v.user_id ORDER BY v.views ASC, v.video_id DESC) as rn
    -- Partition by user, order by views ascending. Tie-break with video_id if needed.
  FROM Videos v
)
SELECT ruv.video_id, u.username, ruv.title, ruv.views, ruv.upload_date
FROM RankedUserVideos ruv
JOIN Users u ON ruv.user_id = u.user_id
WHERE ruv.rn = 1; -- Select the bottom-ranked video (rn=1) for each user

ROW_NUMBER() assigns a unique number within each partition. Using PARTITION BY user_id and ORDER BY views ASC ensures rn = 1 corresponds to the lowest-view video for that user. Other methods involving subqueries or lateral joins exist but can be less performant.

Using ELSE for Defaults and NULL Handling

The optional ELSE clause provides a default value if none of the WHEN conditions are met. If ELSE is omitted and no condition matches, the CASE expression returns NULL.

-- Categorize videos by view count
SELECT
  video_id,
  title,
  views,
  CASE 
    WHEN views >= 1500 THEN 'High Views'
    WHEN views >= 1000 THEN 'Medium Views'
    WHEN views > 0 THEN 'Low Views'
    ELSE 'No Views'
  END AS view_category
FROM Videos;

This categorizes videos based on their view count, with an ELSE clause to handle any videos with 0 or NULL views.

2. Applying Business Logic / Rules

Implement custom rules directly in the query.

-- Determine video age category based on upload date
SELECT
  video_id,
  title,
  upload_date,
  CASE
    WHEN upload_date >= DATE('2023-02-07') THEN 'New'
    WHEN upload_date >= DATE('2023-02-01') THEN 'Recent'
    ELSE 'Old'
  END AS age_category
FROM Videos;

3. Data Transformation / Cleaning

Standardize or clean inconsistent data values.

-- Standardize interaction types
SELECT
  interaction_id,
  CASE interaction_type
    WHEN 'like' THEN 'Like'
    WHEN 'comment' THEN 'Comment'
    ELSE 'Unknown'
  END AS standardized_type,
  timestamp
FROM Interactions;

4. Creating Flags / Indicators

Create boolean-like flags based on conditions.

-- Flag videos based on view count thresholds
SELECT
  video_id,
  title,
  views,
  CASE
    WHEN views > 1000 THEN 1
    ELSE 0
  END AS high_performing_flag
FROM Videos
ORDER BY views DESC;

Conditional Counting (Pivoting)

-- Count interactions by type for each video
SELECT
  v.video_id,
  v.title,
  COUNT(CASE WHEN i.interaction_type = 'like' THEN 1 END) AS like_count,
  COUNT(CASE WHEN i.interaction_type = 'comment' THEN 1 END) AS comment_count
FROM Videos v
LEFT JOIN Interactions i ON v.video_id = i.video_id
GROUP BY v.video_id, v.title
ORDER BY v.video_id;

This pivoting technique uses CASE within COUNT to show the breakdown of interaction types per video.

Conditional Summing

Sum values based on specific criteria.

-- Calculate total views for early vs late February videos
SELECT
  user_id,
  SUM(CASE WHEN upload_date < '2023-02-05' THEN views ELSE 0 END) AS early_feb_views,
  SUM(CASE WHEN upload_date >= '2023-02-05' THEN views ELSE 0 END) AS late_feb_views
FROM Videos
GROUP BY user_id;

Here, CASE selects the views value based on the upload date period, showing the view distribution across early and late February.

Conditional Averaging

Calculate averages for different subsets.

-- Calculate average views for high vs low performing videos per user
SELECT
  user_id,
  AVG(CASE WHEN views >= 1000 THEN views END) AS avg_views_high_performing,
  AVG(CASE WHEN views < 1000 THEN views END) AS avg_views_low_performing
FROM Videos
GROUP BY user_id;

By returning NULL in the CASE when the condition isn't met, we ensure AVG correctly calculates the average only for the relevant subset of videos within each user group.