Skip to main content
Noruj.com
Average Session Duration per App

You are working as a data analyst for a mobile analytics company that tracks user sessions across multiple applications. The platform collects session-level data every time a user opens and closes an app, recording how long each session lasted in seconds. Your database contains a sessions table with the following key columns: session_id (unique identifier for each session), app_name (the name of the mobile application), user_id (the user who initiated the session), and duration_seconds (how long the session lasted in seconds). The product team wants to understand engagement levels across different apps. To help them, you need to calculate the average session duration (in seconds) for each app , so they can identify which apps are keeping users engaged the longest. Return one row per app_name . Calculate the average of duration_seconds for each app. Round the average to 2 decimal places . Name the computed column avg_session_seconds . Order the results by avg_session_seconds in descending order so the most engaging app appears first. Hint: Use the AVG() aggregate function combined with ROUND() to format the result. Remember that ROUND() in PostgreSQL expects a numeric type — you may need to cast the result using ::numeric if you encounter a type error. Group your results using GROUP BY app_name .

Table Setup (SQL Schema)

CREATE TABLE sessions (
  session_id SERIAL PRIMARY KEY,
  app_name VARCHAR(100) NOT NULL,
  user_id INT NOT NULL,
  duration_seconds INT NOT NULL,
  session_date DATE NOT NULL
);

INSERT INTO sessions (app_name, user_id, duration_seconds, session_date) VALUES
('FitTrack', 1, 320, '2024-03-01'),
('FitTrack', 2, 450, '2024-03-01'),
('FitTrack', 3, 210, '2024-03-02'),
('FitTrack', 4, 530, '2024-03-02'),
('FitTrack', 5, 390, '2024-03-03'),
('NewsBlast', 1, 120, '2024-03-01'),
('NewsBlast', 2, 95, '2024-03-01'),
('NewsBlast', 3, 180, '2024-03-02'),
('NewsBlast', 6, 110, '2024-03-02'),
('NewsBlast', 7, 75, '2024-03-03'),
('ShopEasy', 2, 640, '2024-03-01'),
('ShopEasy', 3, 720, '2024-03-01'),
('ShopEasy', 5, 580, '2024-03-02'),
('ShopEasy', 8, 810, '2024-03-02'),
('ShopEasy', 9, 490, '2024-03-03'),
('MindfulMe', 4, 900, '2024-03-01'),
('MindfulMe', 6, 870, '2024-03-02'),
('MindfulMe', 7, 950, '2024-03-02'),
('MindfulMe', 10, 830, '2024-03-03'),
('MindfulMe', 1, 760, '2024-03-03');
HomeVideosBlog