Attestation joins

Count attestations

SELECT e.entry_did, COUNT(a.id) AS n
FROM entries e
LEFT JOIN attestations a ON a.entry_did = e.entry_did
GROUP BY e.entry_did
HAVING n >= 3;

Independence-weighted ranking

SELECT e.entry_did,
       COUNT(DISTINCT a.attester_did) AS independent_count
FROM entries e
LEFT JOIN attestations a ON a.entry_did = e.entry_did
GROUP BY e.entry_did
ORDER BY independent_count DESC;

COUNT(DISTINCT) is the right metric — five attestations from one auditor weigh less than three from three different auditors.

Sugar: ATTESTED_BY

SELECT entry_did
FROM entries
WHERE entry_did ATTESTED_BY 'did:oas:l1fe:auditor:k8s-reproducer'
  TYPE 'build-reproducible';

Equivalent to:

SELECT DISTINCT e.entry_did
FROM entries e
JOIN attestations a ON a.entry_did = e.entry_did
WHERE a.attester_did = 'did:oas:l1fe:auditor:k8s-reproducer'
  AND a.type        = 'build-reproducible';