1

I want to display 3 most viewed photos for 7 days. Let's say that I have 4 images, A, B, C and D.

Number of total views:

A - 300
B - 305
C - 310
D - 400

Number of views in the last 7 days from now:

A - 100
B - 90
C - 95
D - 45

So the 3 most viewed photos for 7 days are A, B and C.

But after 10 minutes it changed! Let's say that someone visited photo D a lot of times, and the table looks like this:

A - 95 (yeah, it can decrease)
B - 92
C - 98
D - 105

Now, the 3 most viewed photos for 7 days are B, C and D.

The question is: how to structure the database? I only have information about a total number of views and the date when the photo was published.

feerlay
  • 2,286
  • 5
  • 23
  • 47
  • You state that you only have information bout the "TOTAL number of views and the date of photo publication" Since you don't know what view occurred on what day, I don't see how you can filter out for 7 days; unless you're getting the summarized data multiple times per day... How frequently are you getting this summarized information? Lastly your question is how to structure the database. Since you only have the photo ID and total number of hits. I see 3 columns 1 for photoID, one for hitcount and one for SummerizedDateTime (and perhaps source if you have multiple sources) – xQbert Feb 21 '18 at 20:09
  • I want to get summarized information as soon as possible. What if every photo had an array of views? And view looks like this `{ who: _user_id_, when: _date_ }`? Then I could filter this array to get only those elements that are in the given time range? – feerlay Feb 21 '18 at 20:27

0 Answers0