Get verified projects that have added an OP address - DROP 10

[divine-comedian]

@mohammadranjbarz you know the drill! We should fetch verified projects that have added an OP recipient address in order to airdrop a new batch of incentives - similar to https://github.com/Giveth/impact-graph/issues/1695

[mohammadranjbarz]

@divine-comedian as I mentioned here https://github.com/Giveth/impact-graph/issues/1695#issuecomment-2241083797 we have more than thousands projects because all endaoment projects have OP address, do you want to exclude them?

Query

SELECT 
    pa."createdAt" as "walletAddressAddedTime",
    pa."projectId",
    pa.address,
    p.slug
FROM 
    project_address pa
JOIN 
    project p ON pa."projectId" = p.id
WHERE 
    pa."networkId" = 10 AND pa."isRecipient" = true AND p.verified = true AND p."statusId"=5
ORDER BY 
    pa."createdAt" ASC;

Result

verified_projects_with_op_address_sep_2024.csv.csv

[divine-comedian]

@mohammadranjbarz yes we should definitely exclude them

[mohammadranjbarz]

Query

SELECT 
    pa."createdAt" AS "walletAddressAddedTime",
    pa."projectId",
    pa.address,
    p.slug
FROM 
    project_address pa
JOIN 
    project p ON pa."projectId" = p.id
LEFT JOIN 
    organization o ON p."organizationId" = o.id
WHERE 
    pa."networkId" = 10 
    AND pa."isRecipient" = true 
    AND p.verified = true 
    AND p."statusId" = 5
    AND (o.label IS NULL OR o.label != 'endaoment')
ORDER BY 
    pa."createdAt" ASC;

Response

verified_project_Wth_op_address_exclude_endaoment_sep2024.csv