How can we calculate the average transaction value for customers who have made more than 10 purchases in the last 90 days?

Question

Anonymous · Accepted Answer

SELECT
AVG(t.transaction_amount) AS average_transaction_amount
FROM
transactions t
WHERE
t.transaction_date >= DATE('now', '-90 days') -- For SQLite, use CURRENT_DATE -
INTERVAL '90 days' for PostgreSQL/MySQL
GROUP BY
t.customer_id
HAVING
COUNT(t.transaction_id) > 10

Anonymous · Answer

WITH 90_agg AS (

SELECT customer_id, count(*) t_count, avg(transaction_amount) avg_amt FROM
transactions WHERE transaction_date >= CURRENT_DATE() - 90 GROUP BY customer_id)

SELECT avg(avg_amt) FROM 90_agg WHERE t_count > 10;

Anonymous · Answer

select sum(amount)/count( distinct transaction_id) as avg_transaction
FROM transactions WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id
HAVING COUNT(transaction_id) > 10;

Anonymous · Answer

SELECT customer_id,
AVG(transaction_amount) AS avg_transaction_amount
FROM transactions WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY customer_id
HAVING COUNT(transaction_id) > 10;

Anonymous · Answer

Here is my made up answer

select cust_id,

avg(txn_amt) as avg_txn_amount

from table

where date >= current_date - 90

having count(*) > 10

How can we calculate the average transaction value for customers who have made more than 10 purchases in the last 90 days?

Practice this question with AI

Go Premium

Practice More Questions

Community Answers

Unlock Community Insights

Unlock Community Insights

Unlock Community Insights

Unlock Community Insights

Unlock Community Insights

Everything You Need to Ace DS Interviews