MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE income
  (
     id       INTEGER,
     ownerid  VARCHAR(10),
     sourceid VARCHAR(20),
     incomeType VARCHAR(10),
     status  VARCHAR(10),
     created  DATE,
     amount   DECIMAL
  );

INSERT INTO income
VALUES      (1,
             'myId',
             'SRC1','passive','paid',
             '2022-05-02',
             100),
            (2,
             'myId',
             'SRC2','passive','paid',
             '2022-05-01',
             300),
            (3,
             'myId',
             'SRC1','passive','paid',
             '2022-05-03',
             100),
            (4,
             'myId',
             'SRC1','passive','paid',
             '2022-05-04',
             300),
              (5,
             'otherId',
             'SRC1','active','paid',
             '2022-05-03',
             100),
            (6,
             'otherId',
             'SRC1','active','paid',
             '2022-05-04',
             300);

Records: 6  Duplicates: 0  Warnings: 0


SELECT *,
       CASE
         WHEN Row_number()
                OVER(
                  PARTITION BY sourceid
                  ORDER BY created ASC) = 1 THEN true
         ELSE false
       END AS isFirstIncome
FROM   income 
WHERE incomeType IN ('passive', 'active') AND status = 'paid' 
ORDER BY created desc


id
ownerid
sourceid
incomeType
status
created
amount
isFirstIncome


4
myId
SRC1
passive
paid
2022-05-04
300
0

6
otherId
SRC1
active
paid
2022-05-04
300
0

3
myId
SRC1
passive
paid
2022-05-03
100
0

5
otherId
SRC1
active
paid
2022-05-03
100
0

1
myId
SRC1
passive
paid
2022-05-02
100
1

2
myId
SRC2
passive
paid
2022-05-01
300
1


SELECT DISTINCT 
  `income`.*,
  CASE WHEN (
      SELECT 
        `income2`.id
      FROM 
          `income` as `income2`
      WHERE
         `income2`.sourceId=`income`.sourceId
      ORDER BY 
          `income2`.created asc
      LIMIT 1
      ) = `income`.id THEN true ELSE false END
  as isFirstIncome
FROM 
  `income` as `income` 
WHERE `income`.incomeType IN ('passive', 'active') AND `income`.status = 'paid'
ORDER BY `income`.created desc
LIMIT 50


id
ownerid
sourceid
incomeType
status
created
amount
isFirstIncome


4
myId
SRC1
passive
paid
2022-05-04
300
0

6
otherId
SRC1
active
paid
2022-05-04
300
0

3
myId
SRC1
passive
paid
2022-05-03
100
0

5
otherId
SRC1
active
paid
2022-05-03
100
0

1
myId
SRC1
passive
paid
2022-05-02
100
1

2
myId
SRC2
passive
paid
2022-05-01
300
1

id	ownerid	sourceid	incomeType	status	created	amount	isFirstIncome
4	myId	SRC1	passive	paid	2022-05-04	300	0
6	otherId	SRC1	active	paid	2022-05-04	300	0
3	myId	SRC1	passive	paid	2022-05-03	100	0
5	otherId	SRC1	active	paid	2022-05-03	100	0
1	myId	SRC1	passive	paid	2022-05-02	100	1
2	myId	SRC2	passive	paid	2022-05-01	300	1