How to visualize max aggregation inside terms aggregation in Kibana?

I have an index exams which contains data related to students who passed some exams.

A student can attempt an exam multiple times. Hence there may be more than one document in the index for a particular student and exam, with a different passedOn date.

I want to create a Kibana Visualization of the count of unique exam completions, ignoring the duplicate values of student-exam pair.

The following query gives the latest date of passing each exam by each student:

GET exams/_search
{
  "size": 0, 
  "_source": false,
  "aggs": {
    "student": {
      "terms": {
        "field": "studentId",
        "size": 20000
      },
      "aggs": {
        "exam": {
          "terms": {
            "field": "examId",
            "size": 1000
          },
          "aggs": {
            "latest": {
              "max": {
                "field": "passedOn"
              }
            }
          }
        }
      }
    }
  }
}

How can I visualize the unique count of student-exam pair, either using the latest passedOn date or using runtime fields or scripted fields or some other way?

Hi @cr_168328 ,

what if instead of focusing on the last value of an exam, you just count the unique_count of passed exams per student?

I mean using the unique count Lens operation, with a filter on passed: true in it, and a break down per student.
Would that work?

Hi @Marco_Liberati ,

Thanks for the reply.

What I want exactly is not a tabular data, but a count of unique student-exam pairs.

For eg., if I have the following data:

Student | Exam | Passed On
s1 | e1 | 2022/01/01
s1 | e1 | 2022/01/02
s1 | e1 | 2022/01/03
s1 | e2 | 2022/01/03
s1 | e2 | 2022/01/04
s1 | e2 | 2022/01/05
s1 | e3 | 2022/01/03
s1 | e3 | 2022/01/04
s2 | e1 | 2022/01/03
s2 | e1 | 2022/01/04
s2 | e1 | 2022/01/05
s2 | e2 | 2022/01/03
s2 | e2 | 2022/01/04

The required result is the count of unique student-exam pairs = 5.

So there's no actual information about passing of an exam, other than the latest PassedOn date?
There's no result field?

No. There is no passed field. Actually, the index contains details of only students who have passed each exam