<p>It can be done with SQL syntax using the <code>sqldf</code> package.</p>
<p>Here is how it works with your example</p>
<pre><code>library(sqldf)

data &lt;- data.frame(id = factor(c(0, 1, 2, 3, 4, 5)),
                   from_id = factor(c(&quot;A&quot;, &quot;B&quot;, &quot;B&quot;, &quot;C&quot;, &quot;C&quot;, &quot;D&quot;)),
                   to_id = factor(c(&quot;B&quot;, &quot;C&quot;, &quot;E&quot;, &quot;B&quot;, &quot;D&quot;, &quot;F&quot;)),
                   amount = c(200, 185, 50, 170, 40, 38),
                   date_trx = c(0708, 0820, 1019, 0508, 0919, 0713)))
</code></pre>
<p>Since all info is from one year, I treated the variable <code>date_trx</code> as month-day numeric, but it could be treated as year-month-day also.</p>
<p>The following query returns what is expected:</p>
<pre><code>sqldf(&quot;select a.*, coalesce(b.suspicious, 'N') as suspicious
       from   data a
       left join (select distinct b.from_id, 'Y' as suspicious
                  from   data a
                  inner join data b
                             on a.to_id = b.from_id and 
                                a.date_trx &lt; b.date_trx and 
                                b.amount/a.amount &gt; 0.9) b
                  on a.from_id = b.from_id&quot;)
</code></pre>
<p>which returns in the R console</p>
<pre><code>  id from_id to_id amount date_trx suspicious
1  0       A     B    200      708          N
2  1       B     C    185      820          Y
3  2       B     E     50     1019          Y
4  3       C     B    170      508          N
5  4       C     D     40      919          N
6  5       D     F     38      713          N
</code></pre>
<p>It seems that you are working to detect fraud, if it is the case, this approach does not works in real-time but you can look at figure 1 in this paper <a href="https://arxiv.org/pdf/2002.05988.pdf" rel="nofollow noreferrer">https://arxiv.org/pdf/2002.05988.pdf</a> to have an idea of how to implement code for doing a real-time suspicious detection.</p>
How to filter the transaction data based on a cutoff value?
<p>This is my transaction data:</p>
<pre><code>
data:

id          from_id        to_id      amount    date_trx
&lt;fctr&gt;      &lt;fctr&gt;         &lt;fctr&gt;     &lt;dbl&gt;     &lt;date&gt;
0           7468           5695       700.0     2005-01-04
1           6213           9379       11832.0   2005-01-08
2           7517           8170       1000.0    2005-01-10
3           6143           9845       4276.0    2005-01-12
4           6254           9640       200.0     2005-01-14
5           6669           5815       200.0     2005-01-20
6           6934           8583       49752.0   2005-01-24
7           9240           8314       19961.0   2005-01-26
8           6374           8865       1000.0    2005-01-30
9           6143           6530       13.4      2005-01-31
...
</code></pre>
<p>I want to filter data as follows:</p>
<p>If at any point in the transfer chain, the ratio of the money received by an account to the money leaving the initial sender is above a threshold value, say 0.9, ( that is 1 ≥ (second_transacted_amount / first_transacted_amount) &gt; 0.9 ), then I want to extract these accounts and save them for later investigation.</p>
<p>For example, here the account &quot;7468&quot; sends 700.0 dollars to the account &quot;5695&quot; in 2005-01-04. After this transaction, let's say &quot;5695&quot; makes a transaction over 90 percent of 700.0 but not exactly 700.0.  Here is another important point: The second transaction should always be ahead of the first. So,  date_trx variable have importance in this case also. How can I do that for the whole data (in R)?</p>
<p>We can have a little dataset to test on:</p>
<pre><code>id          from_id        to_id      amount    date_trx
&lt;fctr&gt;      &lt;fctr&gt;         &lt;fctr&gt;     &lt;dbl&gt;     &lt;date&gt;
0           A              B          200.0     2005-07-08
1           B              C          185.0     2005-08-20
2           B              E          50.0      2005-10-19
3           C              B          170.0     2005-05-08
4           C              D          40.0      2005-09-19  
5           D              F          38.0      2005-07-13
</code></pre>
<p>Considering the transactions from or to B,</p>
<pre><code>0           A              B          200.0     2005-07-08
1           B              C          185.0     2005-08-20
2           B              E          50.0      2005-10-19
3           C              B          170.0     2005-05-08
</code></pre>
<p>due to the following transaction pair, B should be flagged as suspicious</p>
<pre><code>0           A              B          200.0     2005-07-08
1           B              C          185.0     2005-08-20
</code></pre>
<p>With the same reasoning, considering the transactions from or to C</p>
<pre><code>1           B              C          185.0     2005-08-20
3           C              B          170.0     2005-05-08
4           C              D          40.0      2005-09-19
</code></pre>
<p>C shouldn't be flagged as suspicious</p>
<p>Flagging can be done regarding from_id:</p>
<p>The output could be something similar to this:</p>
<pre><code>id          from_id        to_id      amount    date_trx       suspicious
&lt;fctr&gt;      &lt;fctr&gt;         &lt;fctr&gt;     &lt;dbl&gt;     &lt;date&gt;         &lt;fctr&gt;
0           A              B          200.0     2005-07-08      N     
1           B              C          185.0     2005-08-20      Y
2           B              E          50.0      2005-10-19      Y
3           C              B          170.0     2005-05-08      N
4           C              D          40.0      2005-09-19      N   
5           D              F          38.0      2005-07-13      N
</code></pre>