// returns map of video id -> date -> metric name -> double public Dictionary <int, Dictionary <string, Dictionary <string, double> > > VideoMetricByDay(IEnumerable <int> apVideoIds, DateTime start, DateTime end) { using (var context = new ApplicationDbContext()) { var datesInRange = DateUtilities.GetDatesBetween(start, end); var filteredIds = apVideoIds.ToList(); var sourceDaily = from v in context.ApplicationVideos.Where(video => filteredIds.Contains(video.Id)) join avsv in context.ApplicationVideoSourceVideos on v.Id equals avsv.ApplicationVideoId join sv in context.SourceVideos on avsv.SourceVideoId equals sv.Id join daily in context.SourceVideoMetrics.AsNoTracking().Where(m => m.EventDate >= start && m.EventDate <= end) on sv.Id equals daily.VideoId group daily by new { v.Id, daily.EventDate } into x select new { Id = x.Key.Id, EventDate = x.Key.EventDate.Date, Views = x.Sum(m => m.ViewCount ?? 0), Reactions = x.Sum(m => m.ReactionCount ?? 0), }; return(sourceDaily .ToList() .GroupBy(x => x.Id) .ToDictionary( x => x.Key, x => datesInRange.Select( d => new { Date = DateUtilities.ToRestApiDateFormat(d), Metric = new List <Metric>() { new Metric() { Type = "Views", Value = x.SingleOrDefault(dm => d == dm.EventDate)?.Views ?? 0 }, new Metric() { Type = "Reactions", Value = x.SingleOrDefault(dm => d == dm.EventDate)?.Reactions ?? 0 } } }).ToDictionary( z => z.Date, z => z.Metric.ToDictionary(j => j.Type.ToLower(), j => j.Value) ) )); } }
public ActionResult <TrendingResult> TopK( [FromQuery(Name = "k")] int size = 10, [FromQuery(Name = "from")] DateTime?dateStart = null, [FromQuery(Name = "until")] DateTime?dateStop = null, [FromQuery(Name = "sort")] string sortMetric = "Views", [FromQuery(Name = "when")] string when = "custom" ) { sortMetric = sortMetric.ToLower(); DateTime queryDateStart; DateTime queryDateStop; switch (when) { case "custom": queryDateStart = dateStart ?? DateTime.UtcNow.Date.Subtract(new TimeSpan(15, 0, 0, 0)); queryDateStop = dateStop ?? queryDateStart.Subtract(new TimeSpan(-15, 0, 0, 0)); break; case "last_week": queryDateStart = DateTime.UtcNow.Date.Subtract(new TimeSpan(7, 0, 0, 0)); queryDateStop = DateTime.UtcNow.Date; break; case "yesterday": queryDateStart = DateTime.UtcNow.Date.Subtract(new TimeSpan(1, 0, 0, 0)); queryDateStop = queryDateStart; break; case "today": queryDateStart = DateTime.UtcNow.Date; queryDateStop = queryDateStart; break; default: return(BadRequest($"Parameter 'when' must be one of: 'custom', 'last_week', 'today', 'yesterday'")); } var queryRangeLength = (queryDateStop - queryDateStart).TotalDays; // the set of valid parameters is restricted in order to bound impact of query on the system if (size > 20 | size < 1) { return(BadRequest("Parameter 'k' must be one of 1,2,...,20")); } if (!AcceptedSortingColumns.Exists(x => x == sortMetric)) { return(BadRequest($"Cannot sort on '{sortMetric}'. Accepted values: {string.Join(",", AcceptedSortingColumns)}")); } if (queryRangeLength > 90) { return(BadRequest("Selected period cannot be greater than 90 days")); } if (queryRangeLength < 0) { return(BadRequest("Invalid date range")); } var metricList = _dataController.GetMetricList( DateUtilities.ToControllersInputFormat(queryDateStart), DateUtilities.ToControllersInputFormat(queryDateStop) ); var scores = metricList .Select(x => new { id = x.Id, score = x.TotalMetrics.SingleOrDefault(m => m.Type.ToLower() == sortMetric)?.Value ?? 0, m = x }) .OrderByDescending(x => x.score) .Take(size); var dailyBreakDown = _dataBackend.VideoMetricByDay( scores.Select(x => Convert.ToInt32(x.id)), queryDateStart, queryDateStop ); var videoInfo = _dataController.GetVideoList(); var queryResult = ( from s in scores join v in videoInfo on s.id equals v.Id select new VideoTrendingInfo { Title = v.Title, DatePublished = DateUtilities.ToRestApiDateFormat(v.PublishedAt.Date), Tags = Clean(v.Tags), Urls = Clean(v.Sources), Total = AcceptedSortingColumns. ToDictionary( x => x, x => s.m.TotalMetrics .SingleOrDefault(m => m.Type.ToLower() == x) ?.Value ), DailyBreakDown = dailyBreakDown[Convert.ToInt32(s.id)] }).ToList(); if (!queryResult.Any()) { return(BadRequest($"No '{sortMetric}' on the selected period")); } return(Ok(new TrendingResult { From = DateUtilities.ToRestApiDateFormat(queryDateStart), Until = DateUtilities.ToRestApiDateFormat(queryDateStop), K = size, Sort = sortMetric, TopK = queryResult, })); }