// 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,
}));
}