Averaging values and getting standard devs from database to build graph

Viewed 25

Tricky to Explain so Ill shrink down the info to a minimum:

But first, I'll try and explain my ultimate goal, I want to take users who trialed a product and determine how that product affected a value as a percentage compared to their average baseline and then average all these percentages with stand devs.

I have database with the a table that has a user_id, a value, a date.

user_id value date
int int int in epoch miliseconds

I then have a second table which indicates when a trial began and ends for a user and the product they are using for said trial.

user_id start_date end_date product id
int int in epoch milisecs int in epoch milisecs int

What I want to do is gather all the user's trials for one product type, and for each user that participated get a baseline value and their percent change each day. Then take all these percentages and average them and get a standard deviation for each day.

  1. One problem is date needs to convert to days since start_date so anything between the start date and the first 24 hrs will be lumped as day 0, next 24 as day 1, and so forth. So ill be averaging the percents of each day

  2. Not every day was recorded for each user so some will have multiple missing days, so I cant need to mark each day as days from start

  3. The start_date's are random between users

So the graph will look like this:

picture

I would prefer to do as much of it in sql as possible, but the rest will be in Golang.

I was thinking about grabbing each trial , and then each trial will have an array of results. so then I iterate over each trial and iteriate over the results for each trial picking day 0, day 1, day 2 and saving these in their own arrays which I will then average. Everything start getting super messy though

such as in semi pseudo code:

 db.Query("select user_id, start_date from trials where product_id = $1", productId).Scan(&trial.UserId, &trial.StartDate)

//extract trials from rows

for _, trial := range trials {
    // extract leadingAvgStart from StartDate
  db.QueryRow("select AVG(value) from results where user_id = $1 date between $2 and $3", trial.UserId, leadingAvgStart, trial.StartDate)
    // Now we have the baseline for the user

  rows :=  db.Query("select value, date from results where product_id = $1", start)
   //Now we extract the results and have and array
   //Convert Dates to Dates from start Date
   //...? It just start getting ugly and I believe there has to be a better way
}

How can I do most of the heavy lifting with sql?

0 Answers
Related