Напредни извештаи од базата (SQL и складирани процедури)

Извештаи за корисник според тромесечие, просечен број на калории, масти, протеини и јаглени хидрати

SELECT
  U.name AS UserName,
  DATE_TRUNC('quarter', D.date) AS QuarterStart,
  AVG(COALESCE(I.calories, 0)) AS AvgCalories,
  AVG(COALESCE(I.fats, 0)) AS AvgFats,
  AVG(COALESCE(I.protein, 0)) AS AvgProteins,
  AVG(COALESCE(I.carbs, 0)) AS AvgCarbs
FROM
  _User U
JOIN
  UserHasDays UHD ON U.uID = UHD.uID
JOIN
  _Day D ON UHD.dID = D.dID
LEFT JOIN
  DayHasMeal DHM ON D.dID = DHM.dID
LEFT JOIN
  Meal M ON DHM.mID = M.mID
LEFT JOIN
  MealHasIngredient MHI ON M.mID = MHI.mID
LEFT JOIN
  Ingredient I ON MHI.iID = I.iID
GROUP BY
  U.name, DATE_TRUNC('quarter', D.date)
ORDER BY
  U.name, QuarterStart;

Извештаи за корисник според тромесечие, просечно времетраење на тренинг

SELECT
  U.name AS UserName,
  DATE_TRUNC('quarter', TO_TIMESTAMP(UHD.dID)) AS QuarterStart,
  AVG(W.duration) AS AvgWorkoutDurationMins
FROM
  _User U
JOIN
  UserHasDays UHD ON U.uID = UHD.uID
JOIN
  DayHasWorkout DHW ON UHD.dID = DHW.dID
JOIN
  Workout W ON DHW.wID = W.wID
WHERE
  DHW.wID IS NOT NULL
  AND W.duration IS NOT NULL
GROUP BY
  U.name, DATE_TRUNC('quarter', TO_TIMESTAMP(UHD.dID))
ORDER BY
  U.name, QuarterStart;

Извештај за активни корисници според тромесечије

SELECT DATE_TRUNC('quarter', d.date) AS QuarterStart,
       COUNT(DISTINCT uhd.uID) AS num_users_with_activity
FROM _Day d
LEFT JOIN UserHasDays uhd ON d.dID = uhd.dID
LEFT JOIN DayHasWorkout dw ON d.dID = dw.dID
LEFT JOIN DayHasMeal dm ON d.dID = dm.dID
WHERE d.date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY QuarterStart
ORDER BY QuarterStart;

Извештај за број на денови вои кои корисниците биле активни според тромесечије

SELECT uhd.uID,
       DATE_TRUNC('quarter', d.date) AS QuarterStart,
       COUNT(DISTINCT DATE_TRUNC('day', d.date)) AS active_days
FROM _Day d
JOIN UserHasDays uhd ON d.dID = uhd.dID
WHERE d.date >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY uhd.uID, QuarterStart
ORDER BY uhd.uID, QuarterStart;