Can you give a situation where it is preferable to use a direct SQL query instead of ORM or another abstraction layer?

It is preferable to use direct SQL queries in the following situations:

• When a complex or specific query needs to be executed, which is difficult or inefficient to express through ORM (for example, complex aggregate functions, window functions, database-specific constructs).
• To optimize performance when ORM generates suboptimal SQL, and it is necessary to manually write a more efficient query.
• When working with large volumes of data, where it is important to control the exact SQL and indexes.
• To perform operations not supported by ORM, such as calling stored procedures or database-specific commands.

Example in Java using JDBC for a direct SQL query:

String sql = "SELECT COUNT(*) FROM orders WHERE status = 'PENDING'";
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql);
     ResultSet rs = stmt.executeQuery()) {
    if (rs.next()) {
        int count = rs.getInt(1);
        System.out.println("Pending orders: " + count);
    }
}