Location, location, location.

There are three important considerations when deciding where to stay: 1) convenience and facilities at the location, 2) proximity to sightseeing, activities, restaurants/shopping, etc and 3) a room with a view. 
I don’t think you came all this way to spend time in traffic getting to places or to look out & see shrubbery or the back of another bungalow.

The Tahiti Inter-Continental is a full service resort. It is one of the two hotels closest to the airport & also offers easy access to downtown Papeete. It enjoys a prime waterfront location. However, it does not have  a beach as there is none on this part of the island. The majority of its rooms have a lagoon view or panoranic view of the Sea of the Moon and Moorea. (See Day 1 photo)

I recommend the hotel rooms and I stay here myself. I have spent early morning hours on the balcony, sipping complimentary cups of tea from mini bar, mesmerized by that spectacular view, watching the daily activity below: gardeners manicuring the grounds, guests jogging along the path, local canoe clubs paddling back and forth. Honeymoon couples taking those priceless vacation photos.

The Moorea Inter-Continental is close to Opunohu Bay, nestled between the mountains and the sea, with a beachfront location. The individual thatched-roof bungalows are larger than a hotel room with separate living and sleeping sections plus a porch or deck. These can even house families with a twin size day bed and a pull out trundle bed underneath.

Garden units have a view of the tropical landscaping or other bungalows whereas the beach bungalows sit directly at the edge of the sandy beach.
I do recommend the lovely hotel rooms which are centrally located  and well apppointed. However, I would consider upgrading to a beach bungalow for the unique opportunity to live in your own very comfortable “little grass shack” on the beach.

Both resorts do have overwater bungalows. I would describe these as partial or shallow overwater units. These are built on land with a deck that extends out a few feet over the water or they are directly over very shallow water just off shore. For the full overwater experience. there are other more spectacular  locations which I will discuss later.

Of course, other properties have their distinct features and benefits as well but the same points apply.