🎯 𝐒𝐐𝐋 𝐒𝐞𝐫𝐢𝐞𝐬 | 𝐄𝐩. 𝟔 — 𝐂𝐎𝐔𝐍𝐓, 𝐒𝐔𝐌, 𝐀𝐕𝐆, 𝐌𝐈𝐍, 𝐌𝐀𝐗 — 𝐌𝐞𝐚𝐬𝐮𝐫𝐢𝐧𝐠 𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐌𝐞𝐭𝐫𝐢𝐜𝐬

💥 𝐇𝐨𝐨𝐤:
Every metric you see on a business dashboard —
➡ Average session time,
➡ Total orders,
➡ Highest sale,
➡ Number of returning users
all starts with 𝐣𝐮𝐬𝐭 𝐟𝐢𝐯𝐞 𝐒𝐐𝐋 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬.
𝐂𝐎𝐔𝐍𝐓, 𝐒𝐔𝐌, 𝐀𝐕𝐆, 𝐌𝐈𝐍, 𝐌𝐀𝐗 — the unsung heroes of data storytelling.

🧠 𝐂𝐨𝐧𝐜𝐞𝐩𝐭 𝐒𝐢𝐦𝐩𝐥𝐢𝐟𝐢𝐞𝐝:
When we use SQL to answer questions like:
➡ How long does a user stay on our platform?
or
➡ What’s the total revenue by product category?

We’re using 𝐚𝐠𝐠𝐫𝐞𝐠𝐚𝐭𝐢𝐨𝐧 — summarizing detailed data into meaningful metrics. That’s where these 𝐟𝐢𝐯𝐞 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 come in: 𝐂𝐎𝐔𝐍𝐓, 𝐒𝐔𝐌, 𝐀𝐕𝐆, 𝐌𝐈𝐍, 𝐌𝐀𝐗

💼 𝐄𝐱𝐚𝐦𝐩𝐥𝐞: 𝐌𝐞𝐚𝐬𝐮𝐫𝐢𝐧𝐠 𝐀𝐯𝐞𝐫𝐚𝐠𝐞 𝐒𝐞𝐬𝐬𝐢𝐨𝐧 𝐓𝐢𝐦𝐞 (https://lnkd.in/eT57St36)

𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧:
How long does an average user spend in one session?

💡 𝐒𝐐𝐋 𝐒𝐨𝐥𝐮𝐭𝐢𝐨𝐧:

WITH session_times AS (
SELECT
user_id,
DATE(timestamp) AS session_date,
MAX(CASE WHEN action = ‘page_load’ THEN timestamp END) AS load_time,
MIN(CASE WHEN action = ‘page_exit’ THEN timestamp END) AS exit_time
FROM facebook_web_log
GROUP BY user_id, DATE(timestamp) )

Get Somyagupta’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

SELECT
user_id,
AVG(EXTRACT(EPOCH FROM (exit_time — load_time))) AS avg_session_minutes
FROM session_times
WHERE load_time < exit_time
GROUP BY user_id;

🔍 𝐒𝐭𝐞𝐩-𝐛𝐲-𝐒𝐭𝐞𝐩 𝐋𝐨𝐠𝐢𝐜:

1. 𝐈𝐝𝐞𝐧𝐭𝐢𝐟𝐲 each user’s latest page_load and earliest page_exit per day.
2. 𝐂𝐚𝐥𝐜𝐮𝐥𝐚𝐭𝐞 session duration in seconds.
3. 𝐀𝐯𝐞𝐫𝐚𝐠𝐞 these durations across all sessions per user.
➡ 𝐀𝐕𝐆() turns multiple session durations into one business KPI: average time spent per user.

🌍 𝐖𝐡𝐲 𝐈𝐭 𝐌𝐚𝐭𝐭𝐞𝐫𝐬:

In real-world analytics:
1. 𝐄-𝐜𝐨𝐦𝐦𝐞𝐫𝐜𝐞 → 𝐀𝐕𝐆(𝐬𝐞𝐬𝐬𝐢𝐨𝐧_𝐭𝐢𝐦𝐞) helps track engagement.
2. 𝐏𝐫𝐨𝐝𝐮𝐜𝐭 𝐭𝐞𝐚𝐦𝐬 → 𝐒𝐔𝐌(𝐬𝐞𝐬𝐬𝐢𝐨𝐧_𝐭𝐢𝐦𝐞) gives total platform usage.
It’s not just numbers — it’s behavior translated into insights.

🧩 𝐓𝐫𝐲 𝐒𝐢𝐦𝐢𝐥𝐚𝐫 𝐏𝐫𝐨𝐛𝐥𝐞𝐦𝐬:

1️⃣ 𝐍𝐞𝐭 𝐍𝐞𝐰 𝐏𝐫𝐨𝐝𝐮𝐜𝐭𝐬 𝐋𝐚𝐮𝐧𝐜𝐡𝐞𝐝 — https://lnkd.in/e_GK9KaA
2️⃣ 𝐍𝐮𝐦𝐛𝐞𝐫 𝐨𝐟 𝐔𝐧𝐢𝐪𝐮𝐞 𝐒𝐮𝐛𝐣𝐞𝐜𝐭𝐬 𝐓𝐚𝐮𝐠𝐡𝐭 𝐛𝐲 𝐄𝐚𝐜𝐡 𝐓𝐞𝐚𝐜𝐡𝐞𝐫 — https://lnkd.in/esSF5pYM

🔥 𝐂𝐨𝐦𝐢𝐧𝐠 𝐮𝐩 𝐧𝐞𝐱𝐭: 𝐄𝐩. 𝟕 — 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 & 𝐃𝐈𝐒𝐓𝐈𝐍𝐂𝐓: 𝐔𝐧𝐝𝐞𝐫𝐬𝐭𝐚𝐧𝐝𝐢𝐧𝐠 𝐔𝐧𝐢𝐪𝐮𝐞𝐧𝐞𝐬𝐬 𝐢𝐧 𝐃𝐚𝐭𝐚

What’s one business metric you’ve recently calculated using COUNT, SUM, or AVG?
Share it below 👇 — let’s see how you measure success with SQL.