tripz-llc/dashboards/admin/lib/models/feedback_qury.sql

-- Frequent Complaint Passengers
SELECT
    passengers.id AS passenger_id,
    passengers.first_name,
    passengers.last_name,
    passengers.phone,
    COUNT(`feedBack`.id) AS complaint_count
FROM
    passengers
JOIN `feedBack` ON passengers.id = `feedBack`.`passengerId`
GROUP BY
    passengers.id
ORDER BY
    complaint_count
DESC
LIMIT 10;
--==========
-- to get all driver payment to pay to them 
SELECT
    p.driverID,
    COALESCE(SUM(p.amount), 0) AS total_amount,
    COALESCE(SUM(p.amount), 0) + COALESCE(pd.total_points, 0) AS diff
FROM
    payments p
JOIN (
    SELECT
        driverID,
        SUM(amount) AS total_points
    FROM
        paymentsDriverPoints
    WHERE
        payment_method = 'fromBudgetToPoints'
    GROUP BY
        driverID
) pd ON p.driverID = pd.driverID
WHERE
    p.isGiven = 'waiting'
    AND p.payment_method IN ('visa-in', 'visa', 'visaRide', 'TransferFrom', 'payout', 'TransferTo')
GROUP BY
    p.driverID;