0

My app has an assessments table. Each assessment scores people on a variety of levels, and is associated with a group. So something like this:

id: 'some-assessment',
user_id: 'some-user',
group_id: 'some-group',
overall_score: 98,
speed_score: 99,
vision_score: 97

When pulling an assessment from the database, I want to calculate statistics for each score relative to other scores for the same user or group as part of the returned object. As in, I want to return the following:

id: 'some-assessment',
user_id: 'some-user',
group_id: 'some-group',
overall_score: 98,
overall_score_user_percentile: 75, // ie, this score is in the 75th percentile of this users's other scores
overall_score_group_percentile: 60, // 60th percentile for group
speed_score: 99,
speed_score_user_percentile: 72,
speed_score_group_percentile: 63,
vision_score: 97,
vision_score_user_percentile: 71,
vision_score_group_percentile: 65,

I tried to do explore this by generating the following query (based off the use of ntile in an answer to this question:

SELECT
  asmt.*,
  ntile(100) OVER (ORDER BY overall_score) AS overall_score_group_percentile
FROM
  assessments asmt
WHERE
  asmt.id='some-assessment'

Note that this doesn't yet include logic that actually limits the overall_score_group_percentile to other assessments within the group.

Already, I'm running into an issue, which is that it appears that the overall_score_group_percentile calculates the percentile of the current assessment in the current group of assessments -- which is just the current assessment.

In other words, I'm calculating the assessment's percentile within a list of only that one assessment itself. I'm hoping to calculate it within a wider specified list (either of the user's assessments, or the group's).

So I'm always getting 1 as the overall_score_group_percentile.

Any ideas how to calculate the percentile rank of this assessment vs some other set of assessments?

I've tried this as well, in an attempt to widen the comparison group to the full assessments table (before I try to narrow it by group_id):

SELECT
  asmt.*,
  ntile(100) OVER (SELECT overall_score FROM assessments ORDER BY overall_score) AS overall_score_group_percentile
FROM
  assessments asmt
WHERE
  asmt.id='some-assessment'

But no dice. I just get a syntax error.

Thanks! Any help sorting through this would be much appreciated.

Sasha
  • 6,224
  • 10
  • 55
  • 102
  • You don't need a select within the over clause, you already have access to all columns, such as overall_score, within that table. – Paul Maxwell Dec 05 '17 at 23:32

1 Answers1

0

I think you just need a partition by:

SELECT asmt.*,
       ntile(100) OVER (PARTITION BY group_id ORDER BY overall_score) AS overall_score_group_percentile
FROM assessments asmt
WHERE asmt.id = 'some-assessment';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hmm. I tried, that, but it's still returning 1, I think because it's partitioning the list of 1 assessments by `group_id`. Is there a way to create a *different* set of assessments (one of all assessments with a given `group_id` and another with a given `user_id`) , and run the `ntile` function on those collections? – Sasha Dec 05 '17 at 23:42