[SalesForce] how do we query for the Last completed task and Next open task for an account in SOQL

It's a bit of a hack, but you could create a Visualforce page and controller action that will redirect the user to the most recently created Task for a record.

Set the &retURL going into the task creation to send the user to this new VF page and include the parent opporutnity record ID in the query string.

/00T/e?what_id={!Opportunity.Id}&retURL=%2Fapex%2FredirectLastTask?rId= {!Opportunity.Id}

public with sharing class redirectTaskController {
    string recordId = '';

    public redirectTaskController() {
        recordId = ApexPages.currentPage().getParameters().get('rId');
    }

    public PageReference redirectToTask() {
        PageReference activityPage = new PageReference('/' + recordId);

        Task[] recentTasks = [SELECT Id, CreatedDate 
                              FROM Task WHERE WhatId = :recordId 
                              ORDER BY CreatedDate DESC LIMIT 1];

        // Check that the new Task if fairly recent.
        if (recentTasks.Size() == 1 && 
                recentTasks[0].CreatedDate.addHours(1) > Datetime.now()) {
            activityPage = new PageReference('/' + recentTasks[0].Id);
        }

        activityPage.setRedirect(true);
        return activityPage;
    }
}

Visualforce Page

<apex:page controller="redirectTaskController" action="{!redirectToTask}" >
    <p>Redirecting to task</p>
</apex:page>

Another idea would be to create a Visualforce page that uses the Task standardController and provides a custom save action.

You already identified the issue. Exactly, based on the date and once it is surpassed it moves the activities to history table because it would not no longer meaningful but yet it is important for various reports. It is the normal behavior in salesforce and you already find out. There is a field in Task object,

isArchived which is boolean and Salesforce updates it to true once the task is outdated and hence it is history. Its something salesforce managed internally.