analytics.sql

-- Analytics queries for Supabase / PostgreSQL

-- 1. Average rating across all apps.
select round(avg(rating)::numeric,2) as average_rating from reviews;

-- 2. Total number of reviews.
select count(*) as total_reviews from reviews;

-- 3. Count of reviewed apps.
select count(distinct app_name) as reviewed_apps from reviews;

-- 4. Average rating and review count per app.
select app_name,
       round(avg(rating)::numeric,2) as avg_rating,
       count(*) as review_count
from reviews
group by app_name
order by avg_rating desc, review_count desc;

-- 5. Rating distribution.
select rating, count(*) as count
from reviews
group by rating
order by rating desc;

-- 6. Users with the most reviews.
select username,
       count(*) as reviews_submitted
from reviews
group by username
order by reviews_submitted desc
limit 10;

-- 7. Decision suggestion summary.
select case
         when avg(rating) >= 4.5 then 'Strongly recommend'
         when avg(rating) >= 3.5 then 'Recommend'
         when avg(rating) >= 2.5 then 'Consider with caution'
         when avg(rating) >= 1.5 then 'Not recommended'
         else 'Avoid'
       end as decision_suggestion,
       round(avg(rating)::numeric,2) as average_rating
from reviews;