A table sales contains columns (id, sale_date, amount). Consider the query:

SELECT sale_date,
       amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Two rows share the same sale_date. Which behavior is correct in most SQL engines when using the default window frame?

Suppose a table orders has 10 million rows and customers has 1 million rows. Consider two queries:
Query A:

SELECT * 
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Query B:

SELECT *
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Which statement is most accurate for modern cost-based optimizers?

Consider a recursive CTE used to traverse an employee hierarchy:

WITH RECURSIVE emp_tree AS (
   SELECT id, manager_id
   FROM employees
   WHERE manager_id IS NULL

   UNION ALL

   SELECT e.id, e.manager_id
   FROM employees e
   JOIN emp_tree t
   ON e.manager_id = t.id
)
SELECT * FROM emp_tree;

If the table contains a circular relationship (Employee A manages B, and B manages A), what is the most likely result in engines without cycle detection?

Transaction T1 executes:

BEGIN;
SELECT COUNT(*) FROM orders WHERE status='PENDING';

Before T1 commits, Transaction T2 inserts a new PENDING order and commits.

If T1 repeats the same SELECT and still sees the original count, which isolation level is most likely being used?

A table has the following composite index:

INDEX idx_user_activity (user_id, activity_date)

Which query can fully use the index for filtering?