Complex deduplication in PostgreSQL using multiple conditions

user_8610662 0
Viewed 65

I have data on the performance of students MOOCs in a PostgreSQL database. I am trying to munge the data so that I can use it in a marketing funnel-type analysis. The idea is to look the transitions among these stages: how many students sign up, how many open the course, how many take more than half the quizzes, and how many get >90% on all their quizzes.

The problem is that students enroll in courses multiple times, so they get counted multiple times, which makes the numbers wrong.

Here is some sample data

|row | course     | student | percent_progress | percent_points |
|====|============|=========|==================|================|
| 01 | Regression | Ken     | 0.467            | 0.455          |
| 02 | Regression | Ken     | (null)           | (null)         |
| 03 | Regression | Ken     | (null)           | (null)         |
| 04 | Regression | Ryu     | 0.455            | 0.446          |
| 05 | Regression | Ryu     | 0.455            | 0.459          |
| 06 | Clustering | Ryu     | (null)           | (null)         |
| 07 | Regression | Guile   | 0.182            | 0.054          |
| 08 | Regression | Guile   | 0.182            | 0.054          |
|====|============|=========|==================|================|

If I were to manually go through this data, I would pick the "best" course for each person, where "best" is defined as the one with the highest values for percent_progress and percent_points. If the values are (null), then it means they didn't start the course.

Here are a few of the issues I'm facing:

  1. Ken is straightforward because he enrolled in Regression three times but only attempted the course once. Save row 01.
  2. Ryu is complicated because he attempted Regression twice and got the same percent_progress both times, but got more points on row 05. This case throws me off because I have to compare both columns. Nevertheless, we want to keep row 05 for Ryu and row 06 because it is from a different class: Clustering.
  3. Guile is a straight-up duplicate.

The output for the table above would be:

|row | course     | student | percent_progress | percent_points |
|====|============|=========|==================|================|
| 01 | Regression | Ken     | 0.467            | 0.455          |
| 05 | Regression | Ryu     | 0.455            | 0.459          |
| 06 | Clustering | Ryu     | (null)           | (null)         |
| 07 | Regression | Guile   | 0.182            | 0.054          |
|====|============|=========|==================|================|

There are a few different conditions that I need to check for and I am confused about where to even start. Someone recommended using the rank() function and I tried it, but I am doing something wrong because it isn't grouping by course and student, then ranking according to the two performance columns. I also don't know how it would handle cases like Ryu's, where he had a tie for percent_progress and we had to use percent_points. (There are other cases with the opposite pattern, so any hard-coded rule that says to just use percent_points won't work.

Any help is much appreciated.

duplicates postgresql sql
1 Answers

You can use distinct on:

select distinct on (course, student) t.*
from sample t
order by course, student, percent_progress desc nulls last, percent_points desc nulls last;

If you prefer to use, say, the sum of the two values, you can easily adapt that:

order by course, student, (percent_progress + percent_points) desc nulls last
user_11440350
Related