The key difference is that ActiveRecord lets you be more productive by using a higher-level abstraction which lets you write less code.

This abstraction comes at a cost and can lead to performance problems if you don't pay attention to the queries that ActiveRecord generates. N + 1 queries can be a major problem here.

My Observation is if you don't know how to perform an operation with ActiveRecord it is often better to write a bit of SQL with a test backing it and go back and refactor later.

Alright, Let's make our hands dirty.

Here is the schema of our DB which comes handy in wrinting SQL queries as well as rails queries.

Example 1: A simple inner join.

RAW SQL:

select * from customers inner join votes on votes.customer_id = customers.id
where customers.active = 1

Active Record:

Customer.joins(:votes).where(active: true)

Example 2: Inner join with two tables
RAW SQL:

select * from customers inner join posts on posts.customer_id = customers.id inner join comments on comments.post_id = posts.id where customers.active = 1

Active Record:

Customer.joins(posts: :comments).where(customers: { active: true })

Explanation: The amount of code written has been drastically reduced in case of active record.

Example 3: Inner join with three tables.

RAW SQL:

select * from customers cst inner join posts pst on pst.customer_id = cst.id inner join comments cmt on cmt.post_id = pst.id inner join votes v on v.comment_id = cmt.id where cst.active = 1

Active Record:

Customer.joins(posts: [comments: :votes]).where(customers: {active: true})

PS: if you want to get the schema or the rails code you can find the repository here

Hope this read gives you little idea of where to use SQL and rails queries. Please feel free add your thoughts or suggestions :D